02-06-2017 03:49 AM
I'm having trouble calculating the average monthly headcount of employees. My data has the employee number, month. I'm using the DISTINCTCOUNT formula on the employee number column to get the headcount, however i'm not able to get the monthly average. My data looks like this.
as per this data the month-wise headcount is as follows:
|Month||Distinct Count of Employee Number|
therefore my average monthly headcount would be 5. Can someone help me with the formula that can help me create a measure which gives this result.
02-07-2017 07:28 PM
Check this out
If it solves your issue please accept it as a solution and also give KUDOS.
02-15-2017 12:01 PM
I created some sample data to test this out. My assumption is that you could have the same employee in multiple months, so instead of doing a distinctCount, I did a Count. This way you are counting the employee more than once.
I created a measure to get the the employee count:
Employee Count:=COUNT(Sheet1[Employee Number])
I then created a measure that would give me the employee count by months (this is using the previous meausre):
Employee Count by Month:=CALCULATE([Employee Count], all(Sheet1[Date]))
Create a measure to get the total count of months (this is removing all filters on Sheet1, so be aware. But it works in this scenario):
Total Month Count:=CALCULATE(DISTINCTCOUNT(Sheet1[Month]),all(Sheet1))
Get the overall average:
Overall Average Employee Count:=DIVIDE([Employee Count by Month],[Total Month Count])
Hope this works for you.