Help! Discrepancy in KPI Visual for Average Wages per Employee Using Sum and Average Calculations

Q&A
, ,
1

I’m having an issue with a calculated field, with a goal to have a KPI visual that presents Average Wages per Employee.

My data has multiple rows of data per employee. I want to first sum the wages per employee, and then retrieve the average for all employees.

I was able to obtain the correct number in a tabular visual by grouping the data by EMPLOYEEID, and having the WAGES field be aggregated by “SUM” and the Total as “AVERAGE”.

However, when I use the below formula as a calculated field in a KPI visual, the result is off by 2-3%.

I used the following formula:
avg(sumOver(WAGES, [EMPLOYEEID], PRE_AGG))

The odd thing is that I can reproduce the right amount if I filter the data for groups of 2-3 EMPLOYEEIDs, but I can’t get the right number for the full population.

Screenshot 2024-08-30 at 9.13.16 PM
Screenshot 2024-08-30 at 9.13.16 PM2554×1210 290 KB

Does anyone have any ideas to help my troubleshoot?
Any guidance here is appreciated!
Thank you

2

Hi @quicksight44,

Can you try this?

avg(sum(WAGES, [EMPLOYEEID]))
3

Hi @quicksight44,
It’s been awhile since we last heard from you, were you able to find a work around for your case or did you have any additional questions?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

4

Hi @quicksight44,
Since we haven’t heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for any relevant information that may be needed.

Thank you!