## DAX Commands and Tips

Frequent Visitor
Posts: 3
Registered: ‎02-06-2017

# Average of Monthly Distinct Headcount

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.

New Contributor
Posts: 517
Registered: ‎02-17-2016

## Re: Average of Monthly Distinct Headcount

Check this out

https://community.powerbi.com/t5/Desktop/Calculating-average-of-values-from-a-measure-which-have-bee...

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

Cheers

CheenuSing

Highlighted
Member
Posts: 49
Registered: ‎11-10-2016

## Re: Average of Monthly Distinct Headcount

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.