Forum Discussion
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 each level's data. But it ends up the wrong data, like below Pic shows. Can someone help me with this issue?
audreygerred thanks for your solution.
Anonymous thanks your solution. It works perfect. I get expected matrix report by using DAX you provide.
3 Replies
What is the join that you have between your two tables?
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 ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
audreygerred thanks for your solution.
Anonymous thanks your solution. It works perfect. I get expected matrix report by using DAX you provide.
