Forum Discussion
Help With Aggregation
Hello!
I am working on a dashboard that shows ticket counts per 1000 Employees per region, as well as overall data. I got the 'per region' data to look correct, but the overall data is giving me trouble.
My dataset for number of employees is broken out by region and month. What I want to use is a TOTAL average headcount (disregarding region) for a given quarter. I believe since my data is monthly, there is some duplication going on, so my numbers are way out of whack.
In the picture below, the line graph on the right is correct (tickets/1000EE/region). However, the circled number on the bar graph on the left is incorrect.
My measure for tickets/1000 EE is: Sum(Distinct Ticket Count)/(AVERAGE(number of employees)/1000)
I believe the issue is somewhere in the AVERAGE function, but can't seem to get it right. I am thinking I have to create another table with the quarterly headcount data?
For reference, the circled number SHOULD be 195.45, which is calculated as follows
TOTAL AVERAGE EMPLOYEES FOR Q1 2022 = 38158
TOTAL TICKETS FOR Q1 2022 = 7458
Tickets/1000 Employees = 7458/(38159/1000) = 195.45.
I am trying to attach the pbix file here but it is not supported? is it possible to upload somehow?
Any help would be appreciated.
3 Replies
Sum(Distinct Ticket Count)/(AVERAGE(number of employees)/1000)
could be written as
measurename =
DIVIDE (
SUM ( 'table'[Ticket Count] ),
CALCULATE ( AVERAGE ( [number of employees] ) / 1000 ),
removefilter ( 'table'[region] )
)Hello Timmo1016,
Please paste the image of your data, so we can get the idea of data and extrapolate it. Or if you have a gmail account, you can load the pbix in Gmail drive and make it public and share the link using the Insert/Edit Link.
DivKlearner, A bit forward daily.
Link to my channel on Power BI.
https://www.youtube.com/watch?v=aADx2sPA2D0&list=PLDUIC70xSiVQQpfqGhOjBVDF8IBP2k6ob
Here is the headcount data. This is only a snippet. There is data for each month for all 5 regions
Here is the case data. Same thing, there is monthly data for all 5 regions.
I also have a unique region table and a calendar table to relate these two.
Thank you!
