Creating a calculation for Average headcount based on specific c

Resolved · Urgent Priority · Version 365

Thu 2 Nov 2023, 16:17 Delegate Daniel said...

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

Thu 2 Nov 2023, 20:25 Trainer Jens said...

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

Thu 9 Nov 2023: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Power BI tip:

Create Interactive Dashboards

Build interactive dashboards by leveraging features like slicers, drill-through, and bookmarks. Slicers allow users to filter data dynamically, while drill-through enables detailed exploration of specific data points. Bookmarks help you save the current view, making it easy to switch between different states of your report.

View all Power BI hints and tips