Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
alan_joseph
Regular Visitor

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.

2 REPLIES 2
Anonymous
Not applicable

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.

CheenuSing
Community Champion
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors