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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Grellow
Frequent Visitor

Different formula in total row

Hi!

I want a column that uses one calculation on each row and subtotal row, but a different calculation on the total row.

Example from Contoso sample data set:

Grellow_0-1675689268532.png

In the Total row in the right hand column, I want to show the totals of the percentages at Continent level -- in this case 57.23+57.33+57.50 = 172.06.  (I know it's not a realistic example, but it matches something I'm trying to do with real data.)

The [GM pct] variable is just showing Gross Margin as a % of Sales:

Grellow_1-1675689366122.png

I've got this far with the formula for my total:

Grellow_2-1675676624269.png

I know that the IF(HASONEVALUE()) part is working. But I can't find the syntax to sum the values of [GM pct].

Note that [GM pct] is a MEASURE not a column, so I can't use SUM. The visual is summarising a lot of rows in the fact table, and contains a lot of other logic in measures that I don't want to have to recreate by building a new table.

Thanks for any help 😀

1 ACCEPTED SOLUTION

Hi @Grellow 

please try

GM pct total =
SUMX (
SUMMARIZE (
Sales,
Geography[ContinentName],
Product[ProductName],
'Date'[Year]
),
[GM pct]
)

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

Hi @Grellow 
Please use

GM pct total =
SUMX ( VALUES ( Geography[ContinentName] ), [GM pct] )

Hi @tamerj1 

Thanks *SO* much for your reply, that worked perfectly!

Unfortunately I now have a more complicated version of the same puzzle.

Your formula is in the blue cells, and gives exactly what I wanted.

Grellow_0-1676059180803.png

But I now need a second calculation with a different formula, for the red column. 

The calculation I have so far is this one:

Grellow_1-1676059201959.png

I am getting the right results in cells A, B, C — summing up the values at Product.Category level. So, for example, cell B is the sum of the calculations for all the Categories within Europe. That’s what I need.

But in the Grand Total row I also need a sum of A+B+C, which here should be 10.3438.

I can’t get my head around what I would need for that …

Huge thanks again for any help 😀

Hi @Grellow 

please try

GM pct total =
SUMX (
SUMMARIZE (
Sales,
Geography[ContinentName],
Product[ProductName],
'Date'[Year]
),
[GM pct]
)

I'm speechless. Thank you so much 😀.
(I took out the last dimension from the SUMMARIZE to make it exactly what I needed, but I can probably use your formula as well anyway!)

Grellow
Frequent Visitor

I guess a shorter version of my question is:

Can you manipulate the values of MEASURES at different total levels in a visualisation?

I know you can do it to COLUMN values, with SUM and SUMX.

But I’m not sure that DAX has any functions that will allow me to manipulate a MEASURE in this way.

Thanks for any comments 😀

FreemanZ
Super User
Super User

hi @Grellow 

try like:

measure=
IF(
    HASONEVALUE(Geography[ContinentName]),
    [GM pct],
    CALCULATE([GM pct], ALL(Geography[ContinentName]))
)

Hi @FreemanZ . Thanks a lot for trying to help! Your formula returns the same thing as mine -- 57.41%. I've added the definition of [GM pct] into my first post, so you can see what it's doing. 

I think I need my second measure to be summing the values at subtotal level ... somehow! But I don't know how to do that. Thanks again 😀

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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