Forum Discussion

Andres_Maldo's avatar
Andres_Maldo
Frequent Visitor
4 years ago
Solved

Grouping measures in a matrix

Hi, Guys. I have 4 measures (ROI, EBITDA, KPI 1, KPI 2) and I would like to classify in groups:   FINANCE: ROI, EBITDA SALES: KPI 1, KPI 2   And I woud like to get this matrix: I appreci...
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi  Andres_Maldo ,

I created some data:

Here are the steps you can follow：

1. Create caluculated table.

Table 2 =
CROSSJOIN(
    VALUES('Table'[SUPERMARKET]),{"FINANCE","SALES"})

2. Create measure.

ROI_MEASURE = CALCULATE(SUM('Table'[Amount1]),FILTER(ALL('Table'),'Table'[SUPERMARKET]=MAX('Table'[SUPERMARKET])))
EBITDA_MEASURE = CALCULATE(SUM('Table'[Amount2]),FILTER(ALL('Table'),'Table'[SUPERMARKET]=MAX('Table'[SUPERMARKET])))
KPI1_MEASURE = CALCULATE(SUM('Table'[Amount3]),FILTER(ALL('Table'),'Table'[SUPERMARKET]=MAX('Table'[SUPERMARKET])))
KPI2_MEASURE = CALCULATE(SUM('Table'[Amount4]),FILTER(ALL('Table'),'Table'[SUPERMARKET]=MAX('Table'[SUPERMARKET])))
Measure =
SWITCH(
    TRUE(),
    MAX('Table 2'[Value])="FINANCE",CALCULATE([ROI_MEASURE],FILTER('Table','Table'[SUPERMARKET]=MAX('Table 2'[SUPERMARKET]))),
     MAX('Table 2'[Value])="SALES",CALCULATE([EBITDA_MEASURE],FILTER('Table','Table'[SUPERMARKET]=MAX('Table 2'[SUPERMARKET])))  )
Measure 2 =
SWITCH(
    TRUE(),
    MAX('Table 2'[Value])="FINANCE",CALCULATE([KPI1_MEASURE],FILTER('Table','Table'[SUPERMARKET]=MAX('Table 2'[SUPERMARKET]))),
     MAX('Table 2'[Value])="SALES",CALCULATE([KPI2_MEASURE],FILTER('Table','Table'[SUPERMARKET]=MAX('Table 2'[SUPERMARKET])))  )

3. Result:

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly