Forum Discussion
How to Group Measures in Matrix Visualization
I am working on a dashboard to display all the key measures in a same visual. I am thinking to use Matrix. At highlevel I am looking for something like this below.
| FY23 | FY24 | ||||||||||||||
| Measure | P01 | P02 | P03 | P04 | P05 | P06 | P07 | P08 | P09 | P10 | P11 | P12 | P01 | P02 | |
| KPI Group1 | Measure1 | % | % | % | % | % | % | % | % | % | % | % | % | % | % |
| Measure2 | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | |
| KPI Group2 | Measure3 | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit |
| Measure4 | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | |
| Measure5 | % | % | % | % | % | % | % | % | % | % | % | % | % | % | |
| KPI Group3 | Measure6 | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit |
| Measure7 | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | |
| Measure8 | % | % | % | % | % | % | % | % | % | % | % | % | % | % | |
| Measure9 | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | |
| Measure10 | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | |
| Measure11 | % | % | % | % | % | % | % | % | % | % | % | % | % | % | |
| Measure12 | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | Unit | |
| Measure13 | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | $ | |
| Measure14 | % | % | % | % | % | % | % | % | % | % | % | % | % | % |
I already have all the measures created out of various fact tables. The chanllenge I am facing is that the KPI groups are not a dimension exists in any of the fact tables. They are only the how the business leaders want to group the measures based on the nature of the measures themself. How can I achieve this? Thank you in advance for any advice.
3 Replies
netghoster Create a disconnected table with the following data (Enter data query). See table below. You can then create a measure to return your other measures and use that in your matrix. You would use Group and Measure in an ad-hoc hierarchy for your rows.
Measure to Show = VAR __Measure = MAX('Disconnected Table'[Measure]) VAR __Result = SWITCH( __Measure, "Measure1", [Measure1], "Measure2", [Measure2], "Measure3", [Measure3], "Measure4", [Measure4], "Measure5", [Measure5], "Measure6", [Measure6], "Measure7", [Measure7], "Measure8", [Measure8], "Measure9", [Measure9], "Measure10", [Measure10], "Measure11", [Measure11], "Measure12", [Measure12], "Measure13", [Measure13], [Measure14] ) RETURN __ResultGroup Measure KPI Group1 Measure1 KPI Group1 Measure2 KPI Group2 Measure3 KPI Group2 Measure4 KPI Group2 Measure5 KPI Group3 Measure6 KPI Group3 Measure7 KPI Group3 Measure8 KPI Group3 Measure9 KPI Group3 Measure10 KPI Group3 Measure11 KPI Group3 Measure12 KPI Group3 Measure13 KPI Group3 Measure14 Thank you for the help. At least I managed to get the overall report structured the way it supposed to be. The caveat is that I lost the capability to manage the formatting at each individual measure since I have only one measure now. I managed to get the formating worked by using the formatting in the Switch function. I couldn't figure out how to get the conditional formatting to work. I need to color code the cells based on the target range of each measure.
netghoster you can also look into show on rows option for the matrix visual on the format pane.
