Calculate field in row header

[MauricioSD]

Hi All.

I need make a "pivot table" in Power BI, but the info need 3 "measure fields", this are:

% SVM

% Op. Margin

% EBT

But, when i make this measures, y can put in row header in a table.

The example is:

excel attached: 

Example 

Very thank for your help!

[MauricioSD · Accepted solution]

Dear @PaulDBrown 

I can resolve this, with this tutorial:
https://rmarketingdigital.com/powerbi/creacion-de-una-matriz-con-filas-y-columnas-asimetricas-en-pow...

Thanks you !

[PaulDBrown]

Do you need the solution in Excel or PowerBI?

[MauricioSD]

Hi @PaulDBrown , I need solution in Power BI

Very thank you !

[PaulDBrown · Accepted solution]

Ok, first of all, make ysure you Trim and clean the P&L structure field in the table (Power Query).

Then create a new table either in Power Query or using Dax for the row. I've created it using:

P&L Rows = 
{("Sales", 1),
("SVC", 2),
("DSC", 3),
("SVM", 4),
("% SVM", 5),
("OVC", 6),
("CC", 7),
("Other Expenses", 8),
("Op. Margin", 9),
("% Op. Margin", 10),
("NOR", 11),
("EBT", 12),
("% EBT", 13),
("Income Taxes", 14),
("Net Income", 15)}

Join this table with the Main table in an inactive relationship. I've also created dimension Tables for Period and Amount Type. The model looks like this:

Now the measures... Firstly, the main measure:

Sum Amount =
CALCULATE (
    SUM ( FTable[ Amount ] ),
    USERELATIONSHIP ( 'P&L Rows'[Structure], FTable[ PL_Structure ] )
)

Then the measures for the %:

% SVM =
VAR _Sales =
    CALCULATE (
        [Sum Amount],
        FILTER ( ALL ( 'P&L Rows' ), 'P&L Rows'[Structure] = "Sales" )
    )
VAR _SVM =
    CALCULATE (
        [Sum Amount],
        FILTER ( ALL ( 'P&L Rows' ), 'P&L Rows'[Structure] = "SVM" )
    )
RETURN
    DIVIDE ( _SVM, _Sales )

% Op. Margin =
VAR _Sales =
    CALCULATE (
        [Sum Amount],
        FILTER ( ALL ( 'P&L Rows' ), 'P&L Rows'[Structure] = "Sales" )
    )
VAR _OM =
    CALCULATE (
        [Sum Amount],
        FILTER ( ALL ( 'P&L Rows' ), 'P&L Rows'[Structure] = "Op. Margin" )
    )
RETURN
    DIVIDE ( _OM, _Sales )

% EBT =
VAR _Sales =
    CALCULATE (
        [Sum Amount],
        FILTER ( ALL ( 'P&L Rows' ), 'P&L Rows'[Structure] = "Sales" )
    )
VAR _EBT =
    CALCULATE (
        [Sum Amount],
        FILTER ( ALL ( 'P&L Rows' ), 'P&L Rows'[Structure] = "EBT" )
    )
RETURN
    DIVIDE ( _EBT, _Sales )

Now the final measure for the matrix visual:

Table value =
SWITCH (
    SELECTEDVALUE ( 'P&L Rows'[Order] ),
    5, FORMAT ( [% SVM], "Percent" ),
    10, FORMAT ( [% Op. Margin], "Percent" ),
    13, FORMAT ( [% EBT], "Percent" ),
    [Sum Amount]
)

Now you can create the matrix visual using the P&L [Structure] field as rows, the Typ_amount fromt he dimension table as the columns and the [Table Value] as the value to get:

If you want to colour the row headers as your example, you need to use a table visual and split the [Table Value] measure into Actual and Plan following this pattern:

_Actual =
CALCULATE ( [Table value], 'Type'[Type_Amount] = "Actual" )

I've attached the sample PBIX file

[emmalim]

Hi @PaulDBrown , thanks for sharing the solution. I'm currently working on a dashboard for P&L statement, I found this sample file works very well for my scenario. However, my dashboard is required to show various columns with different calculation scenario.

Please allow me to use the sample power bi file to show the challenges I faced, I notice when I add additional calculation (for example add/minus 2 measures) to the column measure, the matrix will have error by having this message '  MdxScript(Model) (31,29) Calculation error in meassure '_measure' [Actual]: Cannot convert value 17.86% of type text to type numeric/date.

Appreciate if anyone can help to point out the root cause and how to resolve, thank you!

[MauricioSD · Accepted solution]

Dear @PaulDBrown 

I can resolve this, with this tutorial:
https://rmarketingdigital.com/powerbi/creacion-de-una-matriz-con-filas-y-columnas-asimetricas-en-pow...

Thanks you !

[PC2790]

Hi @MauricioSD ,

As per my understanding, you are using a matrix similar to below and you are trying to use a measure in the row section which is not possible to add. That's how the matrix works.

You need to add a workaround by creating an intermediate table.

Refer - Creating a “custom” or “hybrid” matrix in PowerBI - Microsoft Power BI Community

https://exceleratorbi.com.au/building-a-matrix-with-asymmetrical-columns-and-rows-in-power-bi/

[MauricioSD]

Hi @PC2790 , 

Yes, I need a custom matrix, in the row fields.

@PaulDBrown can you help me please?, I read you post, but i don't understand

thanks to both

[amitchandak]

@MauricioSD , Check if this workaround from Curbal can help

Curbal - Analyze in excel
https://www.youtube.com/watch?v=IISYzTaIyu4

[MauricioSD]

Hi @amitchandak , this solution is in excel.

My data sources is a Data Warehouse in Azure. I need make this pivot table with the 3 measure, in Power BI.

Thanks you