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