Calculating average employee count

[airwater]

I have created a data model of list of staff reports (x12) converted into one data table. So an employee who has worked for 12 months will appear in the report 12 times.

I have created a new measure which counts the rows for permanent staff and I want to work out the average, I have divided by 12 but that doesn't show accurate results for someone who hasn't worked 12 months.

So Employee 101 was the only worker in department, the  employee worked in January and February but left.  So in this case I want to divide 2/2 instead with the formula below I am getting 2/12 as the average headcount.

Please let me know if unclear, how do I adjust the 12 part of the formula below so it picks up the times the person number has come up in the headcount table. 

In Excel, you can use an anverage if >0

Avg Headcount =

DIVIDE(

AVERAGEX(

    KEEPFILTERS(VALUES('Date Table'[Month])),

    CALCULATE(COUNTROWS(Headcount),Headcount[Assignment Category] = "Permanent")),12,"")

[amitchandak · Accepted solution]

@airwater , This already of month,

AVERAGEX(
KEEPFILTERS(VALUES('Date Table'[Month])),
CALCULATE(COUNTROWS(Headcount),Headcount[Assignment Category] = "Permanent"))

Not sure on the need to divide by 12

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA