Forum Discussion

simolahansen's avatar
simolahansen
Frequent Visitor
8 years ago
Solved

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 |    ...
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
simolahansen
Frequent Visitor
8 years ago

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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