Daniel has attended:
Power BI Modelling, Visualisation and Publishing course
Creating a calculation for Average headcount based on specific c
Hello
I am trying to calculate turnover and sickness for the organisation and then by department, but when the average H/c figure only reflects that of the entire population and not each department
This is the calculation
Avg Headcount =
var summary = SUMMARIZE('Occupancies by Month','Occupancies by Month'[Date], "Headcount", DISTINCTCOUNT('Occupancies by Month'[Person Ref]))
RETURN AVERAGEX(summary, [Headcount])
Can you please advise where I am going wrong
Happy to discuss over the phone if appropriate
Many thanks in advance
Dan
RE: Creating a calculation for Average headcount based on specif
Hi Dan,
Thank you for the forum question.
The SUMMARIZE function create a table so when you RETURN AVERAGEX(summary, [Headcount]) it will be equivalent to DIVIDE([Headcount],COUNTROWS(summary),0). I believe you want to get the average from 'Occupancies by Month' table. I assume you have the Department in this table and this table is your FACT tale.
You can try to click New Table and in the FORMULA bar write
TestMyTable = SUMMARIZE('Occupancies by Month','Occupancies by Month'[Date], "Headcount", DISTINCTCOUNT('Occupancies by Month'[Person Ref]))
Then DIVIDE([Headcount],COUNTROWS(TestMyTable),0) will return the same as your measure and show how many of the [Headcount] divided with number of rows in TestMyTable you have in each department.
I do not know if my explanation is any help, but I cannot tell which DAX can return what you want. I will need to see your data model.
If you send a screenshot of your data model, I may be able to be more helpful.
info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
