Calculating a % on a row subtotal based on a calculated column

[Jonas_Bertels]

Hi,

I have the created a calculated column which takes a profitability % as follows: (Revenues-costs)/Costs-1, as percentage.

This goes without any issues in the data table, but when I try to visualize this in a matrix, I get the following issue:

Cost                         Revenue          Profitability

The individual % are correctly calculated. What I would like to have, is that the subtotal, the line in bold, is taking the same formula and calculating it on the sums of the costs and revenues of the individual parts.

Could anyone help me out on how to correctly use PowerBI for this?

[Anonymous · Accepted solution]

Hi @Jonas_Bertels ,

When you put the calculated column into the matrix, the subtotal is the sum of your calculated column.

534.92%+121.30%+(-100%)=556.22%.

It is suggested to try this measure instead of the calculated column.

Profitability =
DIVIDE (
    SUM ( 'Table'[Revenue] ) - SUM ( 'Table'[Cost] ),
    SUM ( 'Table'[Cost] )
)

You can check more details from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jonas_Bertels]

This is exactly what I needed, Thank you Stephen!

[ryan_mayu]

@Jonas_Bertels 

you can try

percentage = 

if(ISFILTERED('Table'[type]),[revenue_cost]/[_cost]-1,SUMX('Table',[revenue_cost]/'Table'[cost]-1))

[amitchandak]

@Jonas_Bertels , if they are columns try like

divide(Sum(Table[Revenues-costs]) -Sum(Table[Costs]) , Sum(Table[Costs]))

if measure try like
divide((Table[Revenues-costs]) -(Table[Costs]) , (Table[Costs]))

Also refer :https://www.youtube.com/watch?v=ufHOOLdi_jk

[Greg_Deckler]

@Jonas_Bertels This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907