Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Very thank for your help!
Solved! Go to 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 !
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
Proud to be a Super User!
Paul on Linkedin.
Do you need the solution in Excel or PowerBI?
Proud to be a Super User!
Paul on Linkedin.
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
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.
Appreciate if anyone can help to point out the root cause and how to resolve, thank you!
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 !
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
@MauricioSD , Check if this workaround from Curbal can help
Curbal - Analyze in excel
https://www.youtube.com/watch?v=IISYzTaIyu4
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |