topic Re: Different formula in total row in DAX Commands and Tips

Different formula in total row

Anonymous — Mon, 06 Feb 2023 13:18:02 GMT

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 😀

Re: Different formula in total row

FreemanZ — Mon, 06 Feb 2023 11:27:23 GMT

hi Anonymous

try like:

measure=

IF(

HASONEVALUE(Geography[ContinentName]),

[GM pct],

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

)

Re: Different formula in total row

Anonymous — Mon, 06 Feb 2023 13:21:37 GMT

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 😀

Re: Different formula in total row

Anonymous — Tue, 07 Feb 2023 08:36:00 GMT

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 😀

Re: Different formula in total row

tamerj1 — Tue, 07 Feb 2023 11:21:29 GMT

Hi Anonymous
Please use

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

Re: Different formula in total row

Anonymous — Fri, 10 Feb 2023 20:00:38 GMT

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 😀

Re: Different formula in total row

tamerj1 — Fri, 10 Feb 2023 20:15:50 GMT

Hi Anonymous

please try

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

Re: Different formula in total row

Anonymous — Fri, 10 Feb 2023 20:23:13 GMT

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