Calculate % of total with filtering

[Fusilier2]

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:

Hours Absent as % of Total = DIVIDE(CALCULATE([Sum of Hours Absent],ALLSELECTED('Monthly Sickness Data'[Hours Absent ]))

But this doesn't work. Can anybody help or point me in the right direction please?

[Anonymous · Accepted 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

[Fusilier2]

Thank you. However it looks overly complicated and a bit beyond my skills to be honest.