Different data levels in one row

[DieLem]

Good Day,

I have data like this:

I want to create a Matrix that shows the sum of the value. Formula: 

Total Value = sum(Sheet1[Value])

My requirement is to have values from two diffent levels in the same row. Thus summing the Level1 - Colour, and only one of the Level2 - Square. As such:

But I can't get the formatting right. All I can get is this:

Can you please assist?

Thanks!

[prateekraina · Accepted solution]

Hi @DieLem,

Please refer this pbix file.
Hope this solves your problem. Let me know if you want any explanation.

Prateek raina

[DieLem]

Thanks @prateekraina,

I understand the logic. 

Next up I just have to create the custom table with queries and logic on my side.

Thanks so much!

[prateekraina]

Hi @DieLem,

Glad to know that it solved your problem. 
It would be appreciated if you could hit thumbs up for my answer 🙂

Prateek Raina

[Stachu]

try this

Total Value =
VAR ProperFilter =
    FILTER ( Sheet1, Sheet1[Level1] <> "Shape" || Sheet1[Level2] = "Square" )
RETURN
    CALCULATE ( SUM ( Sheet1[Value] ), ProperFilter )

[DieLem]

Hey @Stachu,

Thanks for the feedback. Your solution returns the correct value. 

However that is not my main issue. How do I use the "Matrix" / "Table" visualisation to have Colour and Circle as the only two entries, directly underneath each other?

Thanks!