Change measure value in total row

[Grellow]

Hi!

I want a measure that uses one formula on each row and subtotal row, but a different formula 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 contains a lot of other complex measures that I don't want to recreate by building a new table.

Thanks for any help 😀

[amitchandak]

@Grellow , Better to use isinscope

You have to try GT like

Sumx(Values(Table[Contient Name]), [GM Pct])

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Grellow]

Hi @amitchandak 

Thanks so much for your reply. I couldn't use the ISINSCOPE solution, because I'm not working with tables & columns but only Measures. 

However, your syntax for SUMX(VALUES(... did the job. THANK YOU!

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 😀

[Grellow · Accepted solution]

I realised I posted this in the wrong part of the forum.

This question was answered for me in another thread, here:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Different-formula-in-total-row/m-p/3075379#M1...