Forum Discussion
Matrix with values on different levels
Hello,
I am trying to combine two tables in matrix, where values are on different levels, as following:
Table 1:
| Type | Type 2 | Type 2 | Value1 |
| A | a | aa | 100 |
| b | aa | 200 | |
| c | cc | 200 | |
| B | a | cc | 1000 |
| b | 500 | ||
| C | a | aa | 30 |
| a | bb | 50 | |
| b | aa | 300 | |
| c | aa | 100 |
Table 2:
| Type | Value2 |
| A | 1000 |
| B | 100 |
| C | 200 |
In Table1 Value1 is on third level (Type2), while in Table2 Value2 is on first level (Type). I would like to se matrix, where Value2 is shown only on the first level, and Value1 is in hierarchy - first to third level:
| Type | Value 1 | Type 2 | Type 2 | Value 1 |
| A | 1000 | a | aa | 100 |
| b | aa | 200 | ||
| c | cc | 200 | ||
| B | 100 | a | cc | 1000 |
| b | 500 | |||
| C | 200 | a | aa | 30 |
| a | bb | 50 | ||
| b | aa | 300 | ||
| c | aa | 100 |
Any suggestions? Should I combine two tables in one on primary source?
Thank you!
3 Replies
Hi Anonymous
The first thing you need to do is fill in all the blank values in table one. You can do this cleverly by using Fill Down.
Then you need to create a relationship between the two tables using the Type columns.
The next step is to build a matrix.
In the option under Row headers, turn off Stepped layout and +/- icons.And this is how you put the columns in the matrix settings. Also remember to do a drilldown on the visualization.
The result:
I also prepared file with solution for you. Please use this link: https://gofile.io/d/uxXTGs
_______________
If I helped, please accept the solution and give kudos! 😀Hey lkalawski,
Thanks a lot, your solution helped, but I am looking for the view where Value 1 under Type 2 and Type 2.1 should stay "flexible" - should be enabled to drill down. Is it possible to turn off +/- icons only for Value 2?
Thanks.
Hi Anonymous
Unfortunately not. +/- can only be disabled or enabled for all levels.
_______________
If I helped, please accept the solution and give kudos! 😀
