Forum Discussion

MatthewTu's avatar
MatthewTu
Regular Visitor
2 years ago
Solved

Matrix report show different data on different matrix layers

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 ea...
  • MatthewTu's avatar
    MatthewTu
    2 years ago

     audreygerred  thanks for your solution.

     Anonymous  thanks your solution. It works perfect. I get expected matrix report by using DAX you provide.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.