Forum Discussion
subtotal does not sum, but
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,
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
4 Replies
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 Anonymous
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
