Create measure that replicates SUMIF/matrix aggregation

[Y1009666]

I am sure that there is an easy way to do this but every way I try does not produce the intended result. I have a table with a large variety of categories and a number of columns with values. Unfortunately I can't share the data but here is a very simplified version:

Level 1	Level 2	Level 3	Level 4	Level 5	Country	YTD	Final
Product	Consumables	Food	Fruit	Apples	Germany	10	20
Product	Consumables	Food	Fruit	Oranges	France	5	15
Product	Consumables	Food	Vegetables	Carrots	Spain	10	15
Product	Consumables	Food	Vegetables	Onions	France	0	5
Product	Electronics	Appliances	Televisions	Samsung	Germany	20	30
Product	Electronics	Appliances	Televisions	Phillips	France	15	25

I have these presented in a matrix visual with the levels in this order. 'Country' and many other fields are not present in the visual but are filters; some applied by me, some able to be changed by users in a slicer. I am trying to calculate the percentage of the final values for the YTD values (ie. YTD/Final, so apples in Germany would be 10/20=50%) so I can use this as part of a projection for an upcoming year in which the Final values are not yet known. This is not the problem for most of the rows; the problem is that when I have a situation where the YTD is zero, I want to move the calculation up a level and use the Level 4 aggregated values in place of the Level 5 values. In this example, that would mean that for onions in France, I can't divide 0 by 5, so instead the calculation would use the Vegetables total and divide 10 by 20 to get 50%. In other words, all I want to do is write a measure that usually employs the standard Level 5 calculation, but if that Level 5 YTD is zero, uses the values that the matrix would show by default if I collapsed Level 5 and made it show Level 4 totals instead.

I have tested things like ALL, ALLEXCEPT, ALLSELECTED, REMOVEFILTERS in various combinations in the visual to try and get the sum of all Level 5 values within a Level 4 category. These seem to do either nothing (ie. they produce the same row-level values as the standard sum) or they remove too many of the other filters (ie. they ignore filters like Country). Even when I specify that I want to maintain those filters, I still can't get the Level 5 aggregation to add up to the sum of the values. There are also measure filters on this visual which don't seem to be possible to include in the logic for ALLEXCEPT so I'm not sure it's even possible for me to specify keeping all of the other filters. This seems like it ought to be really easy because I can just see the aggregations I want right there in the matrix, but getting them into a measure is eluding me!

[SamWiseOwl · Accepted solution]

Hi @Y1009666 

% of something =

VAR SYTD = SUM('Table'[YTD])

VAR FYTD = SUM('Table'[Final])

RETURN

SWITCH(

    TRUE()

    ,ISINSCOPE('Table'[Level 5]) && SYTD > 0 --if on the 5 row and has sales

    ,SUM('Table'[YTD])/SUM('Table'[Final])

    ,ISINSCOPE('Table'[Level 4]) && SYTD <> 0 || ISFILTERED('Table'[Level 5]) --row 4 and sales or level 5

    ,CALCULATE(SUM('Table'[YTD])/SUM('Table'[Final]),ALLSELECTED('Table'[Level 5]))

    ,ISINSCOPE('Table'[Level 3]) && SYTD <> 0 || ISFILTERED('Table'[Level 4])

    ,CALCULATE(SUM('Table'[YTD])/SUM('Table'[Final]),ALLSELECTED('Table'[Level 4]))

    ,ISINSCOPE('Table'[Level 2]) && SYTD <> 0 || ISFILTERED('Table'[Level 3])

    ,CALCULATE(SUM('Table'[YTD])/SUM('Table'[Final]),ALLSELECTED('Table'[Level 3]))

    ,ISINSCOPE('Table'[Level 1]) && SYTD <> 0 || ISFILTERED('Table'[Level 2])

    ,CALCULATE(SUM('Table'[YTD])/SUM('Table'[Final]),ALLSELECTED('Table'[Level 2]))

)

[Y1009666]

Amazing, this works just as I wanted, thank you! 

[DataNinja777]

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,

[Y1009666]

Thanks for the reply - this didn't seem to remove the row context from Level 5 and instead was producing the same result as doing the calculation on the standard values. The other solution has worked anyway!