 # Matrix table - multiple levels / % of Total and subtotal

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:

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

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

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 !

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