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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors