topic Re: Incorrect Grand Total for a DAX measure. in DAX Commands and Tips

Incorrect Grand Total for a DAX measure.

MorrisBI — Fri, 09 Sep 2022 23:38:44 GMT

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!

Re: Incorrect Grand Total for a DAX measure.

Chew_WenJie — Sat, 10 Sep 2022 00:24:33 GMT

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.

Re: Incorrect Grand Total for a DAX measure.

amitchandak — Sat, 10 Sep 2022 02:09:44 GMT

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

Re: Incorrect Grand Total for a DAX measure.

tamerj1 — Sat, 10 Sep 2022 08:51:23 GMT

Hi MorrisBI

Please try

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

Re: Incorrect Grand Total for a DAX measure.

MorrisBI — Mon, 12 Sep 2022 14:55:25 GMT

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.

Re: Incorrect Grand Total for a DAX measure.

MorrisBI — Mon, 12 Sep 2022 14:57:36 GMT

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.

Re: Incorrect Grand Total for a DAX measure.

MorrisBI — Mon, 12 Sep 2022 15:03:50 GMT

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.

Re: Incorrect Grand Total for a DAX measure.

tamerj1 — Mon, 12 Sep 2022 15:18:53 GMT

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.