Average of Monthly Distinct Headcounts

[alan_joseph]

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 the average of the four months' headcount which is 5. Can someone help me with the formula that can help me create a measure which gives this result.

TIA.

Alan.

[Vvelarde]

@alan_joseph

hi, you can obtain the result using AverageX

EmployeeNumberByMonth =
DIVIDE (
    AVERAGEX ( Table1, COUNT ( Table1[Employee Number] ) ),
    DISTINCTCOUNT ( Table1[Month] )
)

And like AverageX affect the performance you can use only in totals

EmployeeNumberByMonth =
IF (
    HASONEVALUE ( Table1[Month] ),
    DISTINCTCOUNT ( Table1[Employee Number] ),
    DIVIDE (
        AVERAGEX ( Table1, COUNT ( Table1[Employee Number] ) ),
        DISTINCTCOUNT ( Table1[Month] )
    )
)

[tringuyenminh92]

Hi @alan_joseph,

Are these 2 separated tables? Could you please show me your data model (table structures) ? so I could figure out correct solution.

And one concern that your month-wise headcount is 1 year or multiple years?

[alan_joseph]

Hi @tringuyenminh92, thanks for your reply. The second table is the output i require after the calculations made on the first table. it is nothing but the distinct count of the employee numbers against each month. then i want the average of this column i.e. in this case (5+5+2+8)/4 months = 5 employees per month. 

in reality my data spans for more than 3 years. this  was just a sample i had shared. 

---

[tringuyenminh92 · Accepted solution]

Hi @alan_joseph,

For month-wise table: (Modeling -> new table)

HC_Month_wise = SUMMARIZECOLUMNS(HC[Years],HC[Months],HC[MonthsNo],"Headcount",DISTINCTCOUNT(HC[Employee Number])  )

Create calculated measure for AVG monthly:

AVG Monthly = CALCULATE(AVERAGE(HC_Month_wise[Headcount]),FILTER(all(HC_Month_wise), HC_Month_wise[Years]=max(HC_Month_wise[Years])  ))

Please refer sample file and sample data

[loverlin]

This did not work for me.  Here are the results

To create the calculated column I used

AVG Monthly = CALCULATE(AVERAGE(HC_Month_wise[Headcount]),FILTER(all(HC_Month_wise), HC_Month_Wise[Year]=max(HC_Month_Wise[Year])  ))