Different formula in total row

[Grellow]

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:

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:

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

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 😀

[tamerj1]

Hi @Grellow 
Please use

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

[Grellow]

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.

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

The calculation I have so far is this one:

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 😀

[tamerj1 · Accepted solution]

Hi @Grellow 

please try

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

[Grellow]

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]

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]

hi @Grellow 

try like:

measure=

IF(

    HASONEVALUE(Geography[ContinentName]),

    [GM pct],

    CALCULATE([GM pct], ALL(Geography[ContinentName]))

)

[Grellow]

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 😀