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 appreciate any solutions.

Thanks in advance.

8 Replies

    • Andres_Maldo's avatar
      Andres_Maldo
      Frequent Visitor
      4 years ago

      Hi, ALLUREAN.

      Thanks, but I have measures instead of columns. And I need to be measures. I am wondering is there is a way to group these measure in a matrix.

      • ALLUREAN's avatar
        ALLUREAN
        Icon for Solution Sage rankSolution Sage
        4 years ago

        I use your 4 measures into one, not columns

  • 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