Forum Discussion

Domenico96's avatar
Domenico96
Icon for Helper I rankHelper I
8 months ago
Solved

Total in Matrix with values on row

Hello,  I’m building a custom matrix for a client, and to display the values in rows, I created a custom measure. I couldn’t use the “Switch values to rows” option because it doesn’t export the ...
data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • danextian's avatar
    danextian
    8 months ago

    In one of my screenshots, I'm using a field parameter for the values. I have also attached a pbix in my initial reply, please refer to that. Otherwise, please provide a sanitized (confidential data removed)copy of your own pbix.

Domenico96's avatar
Domenico96
Icon for Helper I rankHelper I
8 months ago

I need to build a matrix with the values on row, as you can see I have a first level that is " Distretto " ( it's a parameter, so it's not fix but dynamic ) and then I have the measure I need : Fatturato, 1 Margine Industriale, 2 Margine Industriale, Margine Operativo Netto e % Margine operativo netto. 

In order to achieve this result, I create a custom table to display this values on row, with this structure : 

Facing Struttura =
DATATABLE(
    "Voce facing", STRING,
    "Ordine", INTEGER,
    {
        {"Fatturato", 1},
        {"1° Margine Industriale", 2},
        {"2° Margine Industriale", 3},
        {"Margine Operativo Netto", 4},
        {"% Margine Operativo Netto", 5}
    }
)

and then added the measure. 

What I need to achieve now is to have a "Total" row that sum up the total of every measure, like in the second screenshot I atteched earlier, but the default option of the matrix are not working as I need, so I need to create a workaround to display this solution. 
Long story shot I need a total for every measure in a row called " total " .
I hope now it more clear
cengizhanarslan's avatar
cengizhanarslan
Icon for Super User rankSuper User
8 months ago

After reading this, I would suggest as below:

 

1) Add a “Total” row to your Facing table

Facing Struttura =
DATATABLE(
    "Voce facing", STRING,
    "Ordine", INTEGER,
    {
        {"Fatturato", 1},
        {"1° Margine Industriale", 2},
        {"2° Margine Industriale", 3},
        {"Margine Operativo Netto", 4},
        {"% Margine Operativo Netto", 5},
        {"Total", 99}
    }
)

Sort [Voce facing] by [Ordine].

 

2) Create your KPI measure (same as you already have)

KPI Value =
SWITCH(
    SELECTEDVALUE('Facing Struttura'[Voce facing]),
    "Fatturato", [Fatturato],
    "1° Margine Industriale", [MI1],
    "2° Margine Industriale", [MI2],
    "Margine Operativo Netto", [MON],
    "% Margine Operativo Netto", [%MON],
    BLANK()
)

 

3) Create the “KPI with Total row” measure

KPI Value (with Total row) =
VAR Voce = SELECTEDVALUE('Facing Struttura'[Voce facing])
RETURN
IF(
    Voce <> "Total",
    [KPI Value],
    [Fatturato] + [MI1] + [MI2] + [MON]
)