Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi
I have a problem
When using matrix and using subtotals I have a similiar matrix as this:
| Price pr. m2 | Buying price | Selling price | Profit | Units Sold | Earned |
Subtotal | 7 | 70$ | 100$ | 30$ | 15 | 450$ |
Room1 | 4 | 20$ | 30$ | 10$ | 5 | 50$ |
Room2 | 10 | 50$ | 70$ | 20$ | 10 | 200$ |
As you can see the earned field in subotatal gets wrong number.
Because the subtotal-function does not sum "Earned" but uses the same formula as on the Rooms (Profit * Units Sold).
So Room1 is correctl Profit*Units Sold, and Room2 but not earned.
It should just sum the Earned-field.
How can I do this?
Solved! Go to Solution.
HI @simolahansen,
Nope, current power bi has different calculation logic than what you want, you had to write a formula to control the calculation on total level.
You goal: calculate formula on row contents first, summary above result on total level.
What Power bi do: summary each row first and use formula calculate on summarized result.
Maybe you can try to use below measures if it works:
Total=
SUM ( Table[Profit] ) * SUM ( Table[Units Sold] )
Earned= IF ( COUNTROWS ( VALUES ( Table[RoomName] ) ) = 1, [Total], SUMX ( VALUES ( Table[RoomName] ), [Total] ) )
Reference link:
Subtotals and Grand Totals That Add Up “Correctly”
Regards,
Xiaoxin Sheng
Hi
I have a problem
When using matrix and using subtotals I have a similiar matrix as this:
| Price pr. m2 | Buying price | Selling price | Profit | Units Sold | Earned |
Subtotal | 7 | 70$ | 100$ | 30$ | 15 | 450$ |
Room1 | 4 | 20$ | 30$ | 10$ | 5 | 50$ |
Room2 | 10 | 50$ | 70$ | 20$ | 10 | 200$ |
As you can see the earned field in subotatal gets wrong number.
Because the subtotal-function does not sum "Earned" but uses the same formula as on the Rooms (Profit * Units Sold).
So Room1 is correctl Profit*Units Sold, and Room2 but not earned.
It should just sum the Earned-field.
How can I do this?
HI @simolahansen,
Current measure formula not works properly on summary levels, you need to add condition to check summary level and write a specific formula for this level.
Sample:
Measure = IF ( ISFILTERED ( Table[RoomName] ), SUMX ( ADDCOLUMNS ( Table, "Earned", [Profit] * [Units Sold] ), [Earned] ), MAX ( Table[Profit] ) * MAX ( Table[Units Sold] ) )
Reference link:
Handling Different Granularities
If above formula not help, please share detail contents to help us clarify your scenario.(e.g. formulas, table structure, sample data)
Regards,
Xiaoxin Sheng
Thank you @v-shex-msft
Is it possible to get this number into Subtotal?
Because Subtotal is a function by PowerBI.
I am using Subtotal because these two rows are grouped by house. And other houses have different rooms. And everything is coming down in a matrix.
Is it possible to change the subtotal-row PowerBI uses?
HI @simolahansen,
Nope, current power bi has different calculation logic than what you want, you had to write a formula to control the calculation on total level.
You goal: calculate formula on row contents first, summary above result on total level.
What Power bi do: summary each row first and use formula calculate on summarized result.
Maybe you can try to use below measures if it works:
Total=
SUM ( Table[Profit] ) * SUM ( Table[Units Sold] )
Earned= IF ( COUNTROWS ( VALUES ( Table[RoomName] ) ) = 1, [Total], SUMX ( VALUES ( Table[RoomName] ), [Total] ) )
Reference link:
Subtotals and Grand Totals That Add Up “Correctly”
Regards,
Xiaoxin Sheng
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.