How to Group Measures in Matrix Visualization

[netghoster]

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.

[parry2k]

@netghoster you can also look into show on rows option for the matrix visual on the format pane.

[Greg_Deckler]

@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
  __Result

Group	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

[netghoster]

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.