Reply
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 NumberMonth
4572771/3/16
4196631/4/16
9680151/2/16
6205721/2/16
5223711/4/16
3457071/1/16
5846261/4/16
2263191/4/16
7519441/4/16
2880361/4/16
4179071/1/16
5786831/1/16
2406421/1/16
5629381/2/16
9119951/3/16
6250271/4/16
5223711/2/16
3457071/4/16
5846261/2/16
2263191/1/16

 

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

 

MonthDistinct Count of Employee Number
Jan5
Feb5
Mar2
Apr8

 

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: 407
Registered: ‎02-17-2016

Re: Average of Monthly Distinct Headcount

Hi @alan_joseph

 

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

Regular Visitor
Posts: 16
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.