Average Head Count of department/Employee over an year and month

[Anonymous]

Hello Everyone,
Greetings!
I was asked to find the Average head count of a department/employee in a Project across the year and month
I have the "Employee" table as below 

I created an a calculated column [pdpt] (Concatenating the project,department, position and type of staff) in the "Employee Table" and also created two additional tables using the summarize function named "Summary Table" and "Connection Table".


"Summary Table" 

 Summary = SUMMARIZE('Employee Table','Employee Table'[Corp Ops],'Employee Table'[Department Code],'Employee Table'[Department Name],'Employee Table'[Function - Copy],'Employee Table'[Project Name],'Employee Table'[Location],'Employee Table'[Function],'Employee Table'[Project Type Description],'Employee Table'[Type of Staff],'Employee Table'[Position],'Employee Table'[PDPT],'Employee Table'[Name],'Employee Table'[Position - Copy.1], "Average Head Count" , AVERAGE('Employee Table'[Head Count]))

Connection Table
Connection Table = Summarize('Employee Table','Employee Table'[PDPT])




Summary[PDPT] --> Connection Table[PDPT] (Many to one, single direction)      
'Employee Table'[PDPT] --> Connection Table[PDPT] ( many to one, bi directional)


After all of this, my report is failing to generate a correct trend by year and month. I would like calculate the average over a year and month for all the employees. Could you please help me with this?

[Anonymous · Accepted solution]

The below DAX helped me to solve it
Average Head Count = Sumx(Distinct[pdpt]), CALCULATE(AVERAGE('vw_CBIP_ManPowerPlan (2)'[Head Count])))

[mahoneypat]

You shouldn't need calculated columns or calculated tables for this one. Just create a measure like this one. It gives the average HC for the distinct combination of department, project, month, and year in the current context.

Avg HC Project Dept Month Year =
AVERAGEX (
SUMMARIZE (
'vw_CBIP_ManPowerPlan (2)',
'vw_CBIP_ManPowerPlan (2)'[Department Code],
'vw_CBIP_ManPowerPlan (2)'[Project Name],
'vw_CBIP_ManPowerPlan (2)'[month],
'vw_CBIP_ManPowerPlan (2)'[Year],
"cHC", SUM ( 'vw_CBIP_ManPowerPlan (2)'[Head Count] )
),
[cHC]
)

Pat

[amitchandak]

@Anonymous , You need  a measure like

Averagex(Values(Date[Month Year]), calculate(Sum(Table[Head Count])) )

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

[Anonymous]

Please find the pbix file in the link below.

https://drive.google.com/file/d/1mHqLZinGPbZ6GO_5v-KQk6dGhPhGNAtz/view?usp=sharing