Matrix report show different data on different matrix layers

[MatthewTu]

Hi guys, I am trying to build a matrix report that can show each layer's data.

I have 2 tables. One is SKU table the other is each sku' demand table.

   

I want matrix report can show each level's data. But it ends up the wrong data, like below Pic shows. Can someone help me with this issue?

[MatthewTu · Accepted solution]

 @audreygerred  thanks for your solution.

 @v-xingshen-msft  thanks your solution. It works perfect. I get expected matrix report by using DAX you provide.

[v-xingshen-msft]

Hi All
Firstly @audreygerred  thank you for you solution !
And @MatthewTu for your question, we can use the ISINSCOPE function to determine the hierarchical structure, we have to start at the bottom to see if it is the lowest level and then recurse up the hierarchy.

Measure_Switch = 
SWITCH(
   TRUE(),

   ISINSCOPE('Table'[Level 5 SKU]),CALCULATE(SUM('Table (2)'[Demand]),'Table (2)'[SKU]=MAX('Table'[Level 5 SKU])),
   ISINSCOPE('Table'[Level 4 SKU]), CALCULATE(SUM('Table (2)'[Demand]),'Table (2)'[SKU]=MAX('Table'[Level 4 SKU])),
   ISINSCOPE('Table'[Level 3 SKU]), CALCULATE(SUM('Table (2)'[Demand]),'Table (2)'[SKU]=MAX('Table'[Level 3 SKU])),
   ISINSCOPE('Table'[Level 2 SKU]), CALCULATE(SUM('Table (2)'[Demand]),'Table (2)'[SKU]=MAX('Table'[Level 2 SKU])),
   ISINSCOPE('Table'[Level 1 SKU]), CALCULATE(SUM('Table (2)'[Demand]),'Table (2)'[SKU]=MAX('Table'[Level 1 SKU])),
   0
)

If you still have questions, check out the Pbix file I uploaded, I hope it helps!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[audreygerred]

What is the join that you have between your two tables?