Forum Discussion

yukon's avatar
yukon
Icon for Helper I rankHelper I
6 years ago
Solved

How to show Grand Total as Column

Hello everyone,   I'm trying show the grand total as column as below screenshot. I write some Dax, however it's given sub total of each values instead of Grand Total. Is it any others way to get sa...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI yukon,

    Perhaps you can add a values filter on Dax formula, then it will group the calculation based on current row label category:

     

    Measure version:
Measure =
CALCULATE (
    SUM ( 'Table'[Amount(EUR)] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Country] )
)

     

    If you want to use calculate column, please check below formula:

    Calculate column version:
Grand Total =
CALCULATE (
    SUM ( 'Table'[Amount(EUR)] ),
    FILTER ( ALLSELECTED ( Table ), Table[Country] = EARLIER ( Table[Country] ) )
)

    Regards,

    Xiaoxin Sheng

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

Hi yukon 

 

You can use ALLSELETCED() like 

Measure = 
CALCULATE(
    SUM( 'Table'[Column1] ),
    ALLSELECTED()
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

yukon's avatar
yukon
Icon for Helper I rankHelper I
6 years ago

Hello Mariusz ,

 

Thank for your comment and DAX sentence. I got grand total for all countries even those countries has no data. Is it possible to show the countries if sum of amount is not zero. 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI yukon,

    Perhaps you can add a values filter on Dax formula, then it will group the calculation based on current row label category:

     

    Measure version:
Measure =
CALCULATE (
    SUM ( 'Table'[Amount(EUR)] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Country] )
)

     

    If you want to use calculate column, please check below formula:

    Calculate column version:
Grand Total =
CALCULATE (
    SUM ( 'Table'[Amount(EUR)] ),
    FILTER ( ALLSELECTED ( Table ), Table[Country] = EARLIER ( Table[Country] ) )
)

    Regards,

    Xiaoxin Sheng