subtotal does not sum, but

[simolahansen]

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?

[simolahansen]

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?

[v-shex-msft]

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

[simolahansen]

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?

[v-shex-msft · Accepted 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