Forum Discussion
how to switch values in matrix with multiple degree rows
Hi, here is my sample looks like:
| version | south | south1 | south2 | north | north1 | north2 |
| A | 20 | 19 | 20 | 18 | 11 | 10 |
| B | 13 | 19 | 17 | 15 | 17 | 12 |
| C | 13 | 15 | 20 | 13 | 16 | 17 |
| D | 20 | 18 | 10 | 15 | 14 | 19 |
and I need to visual it in matrix like this, the row level should remain indentation in matrix,
| A | B | C | D | ||
| south | 11 | 14 | 16 | 13 | |
| south1 | 16 | 18 | 17 | 20 | |
| south2 | 10 | 20 | 13 | 18 | |
| north | 13 | 14 | 16 | 20 | |
| north1 | 11 | 18 | 12 | 19 | |
| north2 | 13 | 11 | 14 | 16 |
I created a dimension table:
| dim_h1 | dim_h2 |
| south | south1 |
| south | south2 |
| north | north1 |
| north | north2 |
and write the measure:
Measure =
SWITCH(TRUE(),
SELECTEDVALUE('dimension'[dim_h1])="south",CALCULATE(SUM('table'[south])),
SELECTEDVALUE('dimension'[dim_h1])="north",CALCULATE(SUM('table'[north])),
SELECTEDVALUE('dimension'[dim_h2])="south1",CALCULATE(SUM('table'[south1])),
SELECTEDVALUE('dimension'[dim_h2])="south2",CALCULATE(SUM('table'[south2])),
SELECTEDVALUE('dimension'[dim_h2])="north1",CALCULATE(SUM('table'[north1])),
SELECTEDVALUE('dimension'[dim_h2])="north2",CALCULATE(SUM('table'[north2]))
)
but the result is like this, it seems level 2 dimension did not return the correct vlaue, and I need to keep a identation in row level. can you help to correct the DAX?
1 Reply
- amitchandak
Super User
Anonymous , better to unpivot the data of the source table, and unpivot columns other than the version
https://radacad.com/pivot-and-unpivot-with-power-bi
Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0gPower Query Table.Unpivot, Table.UnpivotOtherColumns: https://youtu.be/0FEGEAz9UMw
