Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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:

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's avatar
    tamerj1
    3 years ago

    Hi Anonymous 

    please try

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

7 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 
    Please use

    GM pct total =
SUMX ( VALUES ( Geography[ContinentName] ), [GM pct] )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        tamerj1
        Community Champion
        3 years ago

        Hi Anonymous 

        please try

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

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi Anonymous 

    try like:

    measure=
    IF(
        HASONEVALUE(Geography[ContinentName]),
        [GM pct],
        CALCULATE([GM pct], ALL(Geography[ContinentName]))
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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 😀

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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 😀