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,
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?
Solved! Go to 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.
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.
This is exactly what I needed, Thank you Stephen!
you can try
percentage =
if(ISFILTERED('Table'[type]),[revenue_cost]/[_cost]-1,SUMX('Table',[revenue_cost]/'Table'[cost]-1))
Proud to be a Super User!
@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
@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
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |