topic Re: Average of Monthly Distinct Headcount in DAX Commands and Tips

Average of Monthly Distinct Headcount

alan_joseph — Mon, 06 Feb 2017 11:49:42 GMT

Hi,

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.

Employee Number	Month
457277	1/3/16
419663	1/4/16
968015	1/2/16
620572	1/2/16
522371	1/4/16
345707	1/1/16
584626	1/4/16
226319	1/4/16
751944	1/4/16
288036	1/4/16
417907	1/1/16
578683	1/1/16
240642	1/1/16
562938	1/2/16
911995	1/3/16
625027	1/4/16
522371	1/2/16
345707	1/4/16
584626	1/2/16
226319	1/1/16

as per this data the month-wise headcount is as follows:

Month	Distinct Count of Employee Number
Jan	5
Feb	5
Mar	2
Apr	8

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.

TIA.

Alan.

Re: Average of Monthly Distinct Headcount

Anonymous — Wed, 08 Feb 2017 03:28:45 GMT

Hi alan_joseph

Check this out

https://community.powerbi.com/t5/Desktop/Calculating-average-of-values-from-a-measure-which-have-been/td-p/27246

If it solves your issue please accept it as a solution and also give KUDOS.

Cheers

CheenuSing

Re: Average of Monthly Distinct Headcount

Anonymous — Wed, 15 Feb 2017 20:01:21 GMT

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.