Forum Discussion
Calculation at Row Level
- 9 years ago
What you want to do is not as straight forward as it first appears and requires a couple of steps.
1) You need to model your data correctly. By this I mean you need to transpose the names of the measure into columns
2) You need to create individual measure for you Sales, Gross Profit, Operating Profit, Gross Margin and OP%
Gross Margin Amt = DIVIDE([Gross Profit Amt],[Sales Amt]) Gross Profit Amt = SUM(Data[Gross Profit]) OP% = DIVIDE([Operating Profit Amt],[Sales Amt]) Operating Profit Amt = SUM(Data[Operating Profit]) Sales Amt = SUM(Data[Sales])
3) You need to create a seperate table with a list of measure names, don't forget a column to denote the sort order so you calculations will be diplayed in the correct order.
4) You need to create a measure that will display the correct value. This is where the magic happens. This measure will use the SWITCH statement to determine which of the measures (that you created in step 2) to display.
Measure Display = IF ( HASONEVALUE ( 'Measure'[Measure] ), SWITCH ( VALUES ( 'Measure'[Measure] ), "Sales", [Sales Amt], "Gross Profit", [Gross Profit Amt], "Operating Profit", [Operating Profit Amt], "Gross margin",FORMAT( [Gross Margin Amt], "Percent"), "OP%",FORMAT([OP%],"Percent") ) )I have attached a Power BI workbook with a working solution here: One Drive
or you can take a look here: Power BI Publish to Web
What you want to do is not as straight forward as it first appears and requires a couple of steps.
1) You need to model your data correctly. By this I mean you need to transpose the names of the measure into columns
2) You need to create individual measure for you Sales, Gross Profit, Operating Profit, Gross Margin and OP%
Gross Margin Amt = DIVIDE([Gross Profit Amt],[Sales Amt]) Gross Profit Amt = SUM(Data[Gross Profit]) OP% = DIVIDE([Operating Profit Amt],[Sales Amt]) Operating Profit Amt = SUM(Data[Operating Profit]) Sales Amt = SUM(Data[Sales])
3) You need to create a seperate table with a list of measure names, don't forget a column to denote the sort order so you calculations will be diplayed in the correct order.
4) You need to create a measure that will display the correct value. This is where the magic happens. This measure will use the SWITCH statement to determine which of the measures (that you created in step 2) to display.
Measure Display =
IF (
HASONEVALUE ( 'Measure'[Measure] ),
SWITCH ( VALUES ( 'Measure'[Measure] ),
"Sales", [Sales Amt],
"Gross Profit", [Gross Profit Amt],
"Operating Profit", [Operating Profit Amt],
"Gross margin",FORMAT( [Gross Margin Amt], "Percent"),
"OP%",FORMAT([OP%],"Percent")
)
)
I have attached a Power BI workbook with a working solution here: One Drive
or you can take a look here: Power BI Publish to Web