Calculate a contextual abseenteism rate

[Spigaw]

Hello everyone,

I am trying to calculate an absenteeism rate in Power Pivot using DAX. The rate should be calculated as the sum of absence hours divided by the sum of presence hours, and it should be dynamic based on the context. However, my current measure is not working correctly. Here is the DAX formula I am using:

=VAR Numerator = CALCULATE(
    SUM(EVGTA[Durée_Jours]);
    EVGTA[Type] = "Absence"
)
VAR Denominator = CALCULATE(
    SUM(EVGTA[Durée_Jours]);
    EVGTA[Type] = "Présence"
)
RETURN
IF(
    ISBLANK(Numerator) || ISBLANK(Denominator) || Denominator = 0;
    0;
    Numerator / Denominator
)

In the example I am using, please note that:

The value of EVGTA[Type] for HORTHE = "Présence"

The value of EVGTA[Type] for MA and and RT = "Absence"

When I create a pivot table with this measure, the rate is 0.00 % on the line of each absence reason, even if the total is what I am looking for ((660 + 334) / 20657,45):

What I need is a real rate for each line, if we express it with the same example as above:

MA 660,00 3,19 %

RT 344,00 1,67 %

My date column is named EVGTA[Date du jour (détail)], sorry for my French. Of course, I need the rate to be dynamic depending on the context, whatever the dimensions used are.

Thanks for any help!

[Spigaw]

Thanks!!

I don't know why I didn't think about that, I tried ALLEXCEPT, ALLSELECTED, but not REMOVEFILTERS. I just replaced it with ALL as I use Power Pivot, not Power BI for this example.

The final syntax is:

=VAR Numerator =
    CALCULATE ( SUM ( EVGTA[Durée_Jours] ); EVGTA[Type] = "Absence" )
VAR Denominator =
    CALCULATE (
        SUM ( EVGTA[Durée_Jours] );
        EVGTA[Type] = "Présence";
        ALL( EVGTA[Nature d'heure] )
    )
RETURN
    IF (
        ISBLANK ( Numerator ) || ISBLANK ( Denominator )
            || Denominator = 0;
        0;
        Numerator / Denominator
    )

[johnt75 · Accepted solution]

Try

Absenteeism rate =
VAR Numerator =
    CALCULATE ( SUM ( EVGTA[Durée_Jours] ); EVGTA[Type] = "Absence" )
VAR Denominator =
    CALCULATE (
        SUM ( EVGTA[Durée_Jours] );
        EVGTA[Type] = "Présence";
        REMOVEFILTERS ( EVGTA[Nature d'heure] )
    )
RETURN
    IF (
        ISBLANK ( Numerator ) || ISBLANK ( Denominator )
            || Denominator = 0;
        0;
        Numerator / Denominator
    )