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 same outcome with DAX?

 

Please find the attached for your refrences. Sample Excel File 

 

 

Regards,

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

3 Replies

  • 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