Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
simolahansen
Frequent Visitor

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?

1 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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
simolahansen
Frequent Visitor

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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.