Forum Discussion
Solved
Matrix Visual Should show one row rather than summing next levels
Hi all,
I have a situation and I'm assuming it is not possible with straight forward functionalities of PBI.
Please help me in solving this.
I have a data source, something like below.
| Group | LEVEL1 | LEVEL2 | LEVEL3 | LEVEL4 | Value |
| XYZ | Global | 8 | |||
| XYZ | Global | APAC | 8 | ||
| XYZ | Global | APAC | ENT | 3 | |
| XYZ | Global | APAC | ENT | IHE | 0 |
| XYZ | Global | APAC | ENT | M&E1 | 3 |
| XYZ | Global | APAC | ENT | M&E2 | 0 |
| XYZ | Global | APAC | ENT | M&E3 | 6 |
| XYZ | Global | APAC | IMS | 14 | |
| XYZ | Global | APAC | IMS | IMS1 | 14 |
| XYZ | Global | APAC | IMS | IMS2 | 20 |
| XYZ | Global | APAC | IMS | IMS3 | 0 |
| XYZ | Global | APAC | IMS | IMS4 | 6 |
| XYZ | Global | APAC | MED | 0 | |
| XYZ | Global | APAC | MED | ADV | 0 |
| XYZ | Global | APAC | MED | BCS | 0 |
| XYZ | Global | APAC | MED | CAS | 0 |
| XYZ | Global | APAC | MED | PUB | 0 |
| XYZ | Global | NA | 0 | ||
| XYZ | Global | NA | DTS | 0 | |
| XYZ | Global | NA | DTS | ITS | 0 |
| XYZ | Global | NA | WTS | 0 | |
| XYZ | Global | NA | WTS | WTS2 | 0 |
| ABC | Global | 4 | |||
| ABC | Global | APAC | 4 | ||
| ABC | Global | APAC | ENT | 2 | |
| ABC | Global | APAC | ENT | IHE | 0 |
| ABC | Global | APAC | ENT | M&E1 | 2 |
| ABC | Global | APAC | ENT | M&E2 | 0 |
| ABC | Global | APAC | ENT | M&E3 | 3 |
| ABC | Global | APAC | IMS | 7 | |
| ABC | Global | APAC | IMS | IMS1 | 7 |
| ABC | Global | APAC | IMS | IMS2 | 10 |
| ABC | Global | APAC | IMS | IMS3 | 0 |
| ABC | Global | APAC | IMS | IMS4 | 3 |
| ABC | Global | APAC | MED | 0 | |
| ABC | Global | APAC | MED | ADV | 0 |
| ABC | Global | APAC | MED | BCS | 0 |
| ABC | Global | APAC | MED | CAS | 0 |
| ABC | Global | APAC | MED | PUB | 0 |
| ABC | Global | NA | 0 | ||
| ABC | Global | NA | DTS | 0 | |
| ABC | Global | NA | DTS | ITS | 0 |
| ABC | Global | NA | WTS | 0 | |
| ABC | Global | NA | WTS | WTS2 | 0 |
I have dragged them into Matrix Visual like this.
Currently it shows like the screenshot below.
I don't want to show the blank rows coming up in between and also don't want to show totals summed.
I would want to see like below.
Thanks in advance for helping.
Hi mysasai
you can write a measure as follows:
Measure 2 = if (SELECTEDVALUE(My_Table[LEVEL4]) <> blank () , max(My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL3]) <> BLANK() , maxx(filter(My_Table,My_Table[LEVEL4]=blank()) , My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL2]) <> blank() , maxx(filter(My_Table,My_Table[LEVEL3]=blank()) , My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL1]) <> blank() , maxx(filter(My_Table,My_Table[LEVEL2]=blank()) , My_Table[Value]) ,blank()))))and add it to values of your matrix.If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.then it would be better to update your measure as follows:
Measure= if (ISINSCOPE(My_Table[LEVEL4]) , max(My_Table[Value]), if (ISINSCOPE(My_Table[LEVEL3]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) && My_Table[LEVEL1]=SELECTEDVALUE(My_Table[LEVEL1]) && My_Table[LEVEL2]=SELECTEDVALUE(My_Table[LEVEL2]) && My_Table[LEVEL3]=SELECTEDVALUE(My_Table[LEVEL3]) && My_Table[LEVEL4] = blank()),My_Table[Value]) , if (ISINSCOPE(My_Table[LEVEL2]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) && My_Table[LEVEL1]=SELECTEDVALUE(My_Table[LEVEL1]) && My_Table[LEVEL2]=SELECTEDVALUE(My_Table[LEVEL2]) && My_Table[LEVEL3] = blank()),My_Table[Value]) , if (ISINSCOPE(My_Table[LEVEL1]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) && My_Table[LEVEL1]=SELECTEDVALUE(My_Table[LEVEL1]) && My_Table[LEVEL2] = blank()),My_Table[Value]) , if (ISINSCOPE(My_Table[Group]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) && My_Table[LEVEL1] = blank()),My_Table[Value]))))))and easily can filter blank in the filter pane of matrix visual as follows"do the same for each level. if any Q feel free to ask.If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.
4 Replies
- Selva-Salimi
Solution Sage
Hi mysasai
you can write a measure as follows:
Measure 2 = if (SELECTEDVALUE(My_Table[LEVEL4]) <> blank () , max(My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL3]) <> BLANK() , maxx(filter(My_Table,My_Table[LEVEL4]=blank()) , My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL2]) <> blank() , maxx(filter(My_Table,My_Table[LEVEL3]=blank()) , My_Table[Value]) , if (SELECTEDVALUE(My_Table[LEVEL1]) <> blank() , maxx(filter(My_Table,My_Table[LEVEL2]=blank()) , My_Table[Value]) ,blank()))))and add it to values of your matrix.If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.Thank you Selva-Salimi, this worked and values are not suming up.
Any idea how would I hide blank rows highlighted in Red lines in screenshot.
- Selva-Salimi
then it would be better to update your measure as follows:
Measure= if (ISINSCOPE(My_Table[LEVEL4]) , max(My_Table[Value]), if (ISINSCOPE(My_Table[LEVEL3]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) && My_Table[LEVEL1]=SELECTEDVALUE(My_Table[LEVEL1]) && My_Table[LEVEL2]=SELECTEDVALUE(My_Table[LEVEL2]) && My_Table[LEVEL3]=SELECTEDVALUE(My_Table[LEVEL3]) && My_Table[LEVEL4] = blank()),My_Table[Value]) , if (ISINSCOPE(My_Table[LEVEL2]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) && My_Table[LEVEL1]=SELECTEDVALUE(My_Table[LEVEL1]) && My_Table[LEVEL2]=SELECTEDVALUE(My_Table[LEVEL2]) && My_Table[LEVEL3] = blank()),My_Table[Value]) , if (ISINSCOPE(My_Table[LEVEL1]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) && My_Table[LEVEL1]=SELECTEDVALUE(My_Table[LEVEL1]) && My_Table[LEVEL2] = blank()),My_Table[Value]) , if (ISINSCOPE(My_Table[Group]) , maxx(FILTER(ALL(My_Table) ,My_Table[Group]=SELECTEDVALUE(My_Table[Group]) && My_Table[LEVEL1] = blank()),My_Table[Value]))))))and easily can filter blank in the filter pane of matrix visual as follows"do the same for each level. if any Q feel free to ask.If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.
