Calculation at Row Level

[rajibmahmud]

Can someone suggest how can I have Value calculated in a new measure at Row which is not part of Datatable.

Below is source table:

Product	Measure	Month	Value
A	Sales	Jan	100
A	Gross Profit	Jan	60
A	Operating Profit	Jan	40
A	Sales	Feb	200
A	Gross Profit	Feb	120
A	Operating Profit	Feb	90
A	Sales	Mar	300
A	Gross Profit	Mar	170
A	Operating Profit	Mar	120

I need to calculate Gross Margin & OP% from the table and show in below format.

	Jan	Feb	Mar
Sales	100	200	300
Gross Profit	60	120	170
Operating Profit	40	90	120
Gross Margin	60.0%	60.0%	56.7%
OP%	40.0%	45.0%	40.0%

[OpenDataLab · Accepted solution]

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

[sam_gift]

Hi @OpenDataLab  Is there a possibility that we can implement this way for a matrix? I have a similar requirement where as I have two levels of matrix defining this way. 

[rajibmahmud]

I cant see the file in Onedrive 😞

[OpenDataLab]

Please try downloading from this link: One Drive.  If this is what your looking for, please mark as Solution.

Let me know how you get on.

[rajibmahmud]

@OpenDataLab

This will do perfectly. However I was wondering will it be possible to do it without transposing the table as I will need this for other purpose. 

One option could be if I can use something similar to SUMIFS of Excel to calculate below. I am new in DAX, so I am not sure whether there is option to do that or not...

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

[rajibmahmud]

I made it work by adding entire formula in a calculated measure, where I used Calculate to get value from table and for calculate measure entire divide formula is written using Divide & calculate combination. Now my measure have 2 pages long formula but its working.

[Greg_Deckler]

I solved a similar issue here:

https://community.powerbi.com/t5/Desktop/This-week-in-Dax/td-p/211393

[OpenDataLab]

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