How to show Grand Total as Column

[yukon]

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,

[Mariusz]

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]

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 · Accepted solution]

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