Matrix table - multiple levels / % of Total and subtotal

DAX DAX Calculations
1

Hi!

I have a question which I cant find the answer to in all the videos on the site. I would be most grateful if someone has a solution for this one, or can point me towards a resource that will help me figure it out for myself:

Picture of Desired Result vs current outcome attached:

enterpriseq
enterpriseq.png764×286 6.07 KB

The Measures I have created are as follows

Values = SUM (Fact Table[Sales])

a simple sum of the column to fetch the total sales data for everything

Values Unfiltered = Calculate ( [VALUES], ALL (ProductTable[Range Level 2]) )

in order to remove any filters in my table that filter the products

% of Total = DIVIDE ([Values], [Values Unfiltered])

in order to calculate for each row, the relevant % of the total

As expected this means that the SUBTOTALS for Range 2 are incorrect and hence so are the %'s - the desired result is that a single measure is created which lifts the filter applied for each row/category.

Is there a solution to this where there are > 2 levels in my matrix visual? Perhaps its 2 steps that lift each filter and then chooses one to use depending on the context in the table?

Many thanks!

James

2

Interesting one.

I think that you may need to add in some IF logic here…or SWITCH logic.

For example.

IF( ISFILTERED( RangeLevel2Column), Values Unfiltered Measure,
    IF ( ISFILTERED( RangeLevel3Column, Next Values Unfiltered Measure......etc

You could also add these into a SWITCH function also as mentioned.

Potentially you could make the Values Unfiltered measures as Variables as well in the one measure.

Can you give this a go first.

If no luck, can you add a small sample of the data and I can have a further play around.

Chrs

3

Ah i hadnt thought of that. worked perfectly, here is the code that was successful

Unfiltered at the right level =
IF (
    AND (
        ISFILTERED ( Range 2) = TRUE (),
        ISFILTERED ( Range 3 ) = FALSE ()
    ),
    CALCULATE (
        Sales,
        ALL (
            Range 3,
            Range2
        )
    ),
    CALCULATE (
       Sales,
        ALL ( Range3 )
    )
)

thanks alot sam! very useful !

4

Nice one. Looks great.

Personally not a big fan of DAX formatter on this one though…it makes the formula too elongated in my view.

Here’s a few ideas that I think are better for formatting etc.

1 Like