Count by Aggregates

Hey all, I’m attempting to create a KPI card that will allow to count employees by their average scores. Here is what the data looks like-

I’m trying to acheive the highlighted output but I’m not able to formulate by category for some reason.

Hello @Ops_Expert , I was thinking of some ways to get to your desired value for the KPI and I think I have an idea that will work. Try this these calculated fields below:

First calculated field will create the average for each user and set a value as 1 or 2 depending on your requirements

  • Calc1 - ifelse(sum(rating, [Member])/3 < 2, 1, 2)

Then you will create 2 more calculated fields, 1 for each KPI (Greater than or less than 2)

  • sumIf({calc1}, calc1 = 1)
  • sumIf({calc1}/2, calc1 = 2)

It is a bit of a work around but based on my testing, this should give you the result you are looking for. You may need to divide the final value by 3 because the number might be triple the count due to there being 3 rows for each member.

1 Like

For now, I created another column by taking average by employee (partition by) during Custom SQL import. Works fine. The calculation gets more complex with nulls, etc. Will test this. Thanks

1 Like

Hello @Ops_Expert , I wanted to reach out and follow up. Did your solution end up working as you expected? If so I can go ahead and close this topic!

1 Like