Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm stuck with DAX. Hopefully this is simple to achieve.
I have three department levels in a hierarchy:
Director dept
Service
Cost Centre
I think I need two DAX measures:
1. I want to calculate Hours Absent as a % of total Hours absent for each level when filtered
So, if total sickness days for the company as a whole was 100 and I select Director Dept 'A' where total Hours absent was 50, the measure would return 50% and so on for each level in the hierarchy as drilled down.
2. I also want a measure that will calculate the % at each level as a proportion of the total at that level.
For example Service dept B/Director dept A where Service dept B is part of Director dept A.
I've tried this measure:
Solved! Go to Solution.
Hi, @Fusilier2
Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.
Measure:
Absent days all % =
VAR _director =
CALCULATE ( SUM ( 'Director dept'[Absent Days] ) )
VAR _service =
CALCULATE ( SUM ( Service[Absent] ) )
VAR _costCenter =
CALCULATE ( SUM ( 'Cost Center'[Absent] ) )
VAR _dept =
SELECTEDVALUE ( 'Company dept A'[Dept A] )
VAR _allAbesent = _costCenter + _director + _service
RETURN
SWITCH (
_dept,
"Cost Center", DIVIDE ( _costCenter, _allAbesent ),
"Director dept", DIVIDE ( _director, _allAbesent ),
"Service", DIVIDE ( _service, _allAbesent ),
"Pls Select Dept"
)
Absent days level % =
VAR _director =
CALCULATE ( SUM ( 'Director dept'[Absent Days] ) )
VAR _service =
CALCULATE ( SUM ( Service[Absent] ) )
VAR _costCenter =
CALCULATE ( SUM ( 'Cost Center'[Absent] ) )
VAR _deptA =
SELECTEDVALUE ( 'Company dept A'[Dept A] )
VAR _deptB =
SELECTEDVALUE ( 'Company dept B'[Dept B] )
VAR _A =
SWITCH (
_deptA,
"Cost Center", _costCenter,
"Director dept", _director,
"Service", _service
)
VAR _B =
SWITCH (
_deptB,
"Cost Center", _costCenter,
"Director dept", _director,
"Service", _service
)
RETURN
DIVIDE ( _A, _B )
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, @Fusilier2
Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.
Measure:
Absent days all % =
VAR _director =
CALCULATE ( SUM ( 'Director dept'[Absent Days] ) )
VAR _service =
CALCULATE ( SUM ( Service[Absent] ) )
VAR _costCenter =
CALCULATE ( SUM ( 'Cost Center'[Absent] ) )
VAR _dept =
SELECTEDVALUE ( 'Company dept A'[Dept A] )
VAR _allAbesent = _costCenter + _director + _service
RETURN
SWITCH (
_dept,
"Cost Center", DIVIDE ( _costCenter, _allAbesent ),
"Director dept", DIVIDE ( _director, _allAbesent ),
"Service", DIVIDE ( _service, _allAbesent ),
"Pls Select Dept"
)
Absent days level % =
VAR _director =
CALCULATE ( SUM ( 'Director dept'[Absent Days] ) )
VAR _service =
CALCULATE ( SUM ( Service[Absent] ) )
VAR _costCenter =
CALCULATE ( SUM ( 'Cost Center'[Absent] ) )
VAR _deptA =
SELECTEDVALUE ( 'Company dept A'[Dept A] )
VAR _deptB =
SELECTEDVALUE ( 'Company dept B'[Dept B] )
VAR _A =
SWITCH (
_deptA,
"Cost Center", _costCenter,
"Director dept", _director,
"Service", _service
)
VAR _B =
SWITCH (
_deptB,
"Cost Center", _costCenter,
"Director dept", _director,
"Service", _service
)
RETURN
DIVIDE ( _A, _B )
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Thank you. However it looks overly complicated and a bit beyond my skills to be honest.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
38 | |
26 | |
20 | |
15 | |
8 |
User | Count |
---|---|
69 | |
47 | |
46 | |
20 | |
15 |