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 ...
  • 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]
    )

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper 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 😀