Help With Aggregation

[Timmo1016]

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.

[DivkLearner]

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

[Timmo1016]

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!

[vanessafvg]

 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] )
)