Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.