98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Power BI training and help » Creating a calculation for Average headcount based on specific c
Creating a calculation for Average headcount based on specific c
Resolved · Urgent Priority · Version 365
Thu 2 Nov 2023, 16:17Delegate 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:25Trainer 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.
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Create Interactive DashboardsBuild 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. |
