Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
MauricioSD
Helper I
Helper I

Calculate field in row header

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 

 

MauricioSD_0-1649680630716.png

 

Very thank for your help!

2 ACCEPTED SOLUTIONS

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)}

PLstructure.jpg

 

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:

model.jpgNow 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:

matrix.jpgIf 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" )

table.jpg

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

9 REPLIES 9
PaulDBrown
Community Champion
Community Champion

Do you need the solution in Excel or PowerBI?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi @PaulDBrown , I need solution in Power BI

 

Very thank you !

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)}

PLstructure.jpg

 

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:

model.jpgNow 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:

matrix.jpgIf 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" )

table.jpg

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

emmalim_1-1691924735349.png

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

PC2790
Community Champion
Community Champion

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/

 

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
Super User
Super User

@MauricioSD , Check if this workaround from Curbal can help

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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors