cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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] ) )

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.
4 REPLIES 4
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?

Community Support

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] )
)

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.
Frequent Visitor

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?

Community Support

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] ) )

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors