Incorrect Grand Total for a DAX measure.

[MorrisBI]

HI everyone!

I have a DAX measure that does not sum the entire column, just the second subtotal, here is the image of the code and the example of the column with the error:

Gastos totales = 
    sumx (
        values('Calendario2'[Mes]),
        sum(GastosFijos[Gasto]
 ))

I looked for this issue and solve some of the errors with the combination of SUMX and values, but for a strange reason the Grand Total keeps showing wrong, sometimes I see that is the total of the dataset but here is not the case, I'm not applying any filter at all neither.

Thanks!

[tamerj1 · Accepted solution]

Hi @MorrisBI 

Please try

Gastos totales =
SUMX (
    SUMMARIZE (
        'Calendario2',
        'Calendario2'[Año],
        'Calendario2'[Trimestre],
        'Calendario2'[Mes]
    ),
    CALCULATE ( SUM ( GastosFijos[Gasto] ) )
)

[MorrisBI]

Hi

This work.

But, please, can you tell my why is summarize the best options instead of value? I simple don't understood as I should.

[tamerj1]

@MorrisBI 

To create a virtual table that simulates the matrix visual and then iterate over it. This will guarantee we are summing the visible values in the matrix.  

[amitchandak]

@MorrisBI , Refer to suggestion from @Chew_WenJie . But I think you need to use calculate in expression as you have used aggregation

Gastos totales =
sumx (
values('Calendario2'[Mes]),
calculate(sum(GastosFijos[Gasto])
))

[MorrisBI]

Hi

I did it but doesn't work, the outcome is the same with the error on Grand Total.

I suspect, my mistake is in the second line but not sure how to fix it.

[Chew_WenJie]

Hihi, maybe you can try to change the column of the values 

values('Calendario2'[Mes]),

to other columns to get the correct subtotals.

Hope this can help you.

[MorrisBI]

Hi

Changing from Mes (Month) to another one, let say Dia (Day) just will end up giving me another numbers with the same error in the Grand Total.