Create measure that replicates SUMIF/matrix aggregation

Hi Y1009666 ,

To handle this scenario in DAX, we want a measure that first calculates the percentage of YTD over Final at the most granular level (Level 5). However, if the YTD value is zero, we want the calculation to fall back to the next level up (Level 4), summing the YTD and Final values for the entire Level 4 group while still respecting any external filters like Country. Here's how you can write the DAX measure to accomplish that:

Projection % =
VAR YTD_Level5 = CALCULATE(SUM('YourTable'[YTD]))
VAR Final_Level5 = CALCULATE(SUM('YourTable'[Final]))
VAR YTD_Level4 = CALCULATE(SUM('YourTable'[YTD]), REMOVEFILTERS('YourTable'[Level 5]))
VAR Final_Level4 = CALCULATE(SUM('YourTable'[Final]), REMOVEFILTERS('YourTable'[Level 5]))
RETURN
    IF(
        YTD_Level5 <> 0 && Final_Level5 <> 0,
        DIVIDE(YTD_Level5, Final_Level5),
        DIVIDE(YTD_Level4, Final_Level4)
    )

This measure checks if the Level 5 YTD is non-zero and uses that value directly. If the YTD is zero (or Final is zero), it removes the Level 5 filter to compute the totals for Level 4 instead, while still keeping all other filters in place (like Country or anything else selected in slicers). This ensures that you get accurate fallback aggregation that matches what the matrix visual would show if you collapsed Level 5. Let me know if you need a fallback to Level 3 as well—this can be extended further with the same logic.

Best regards,