Total Column doesn't match

[geisla_melo]

Hi everyone!

I have a monthly value, and I need a measure that shows the actual monthly value less the last month value.
Except in january, cause it has to be the actual monthly value (I can't consider the past year value). When it gets to a month with 0, I can't do the subtraction to not have negative value.

I have this result, except for the totals, that keep making this substraction, showing a total 0 (zero).

This is how it looks like, I need the last column showing the total 51.117:

These are my measures:
CH Mês Anterior: 

CALCULATE(SUM(CH Original), DATEADD(Periodo[Data], -1, MONTH), Periodo[Ano] = SELECTEDVALUE(Periodo[Ano]))

-------
CH Original Menos Mês Anterior:

CALCULATE(sumx(CargaHorariaAluno, CargaHorariaAluno[CH Original])) -

CALCULATE(SUMX(CargaHorariaAluno, CargaHorariaAluno[CH Original]), DATEADD(Periodo[Data], -1, MONTH), Periodo[Ano] = SELECTEDVALUE(Periodo[Ano]))

-------- 
CH Original Menos Mês Anterior Sem Negativo:

VAR UltimoMes = CALCULATE(Max(Periodo[Mês]),

FILTER(CargaHorariaAluno, CargaHorariaAluno[CH Original] > 0))

return

CALCULATE(sumx(CargaHorariaAluno, CargaHorariaAluno[CH Original])) -

CALCULATE(SUMX(CargaHorariaAluno, CargaHorariaAluno[CH Original]), DATEADD(Periodo[Data], -1, MONTH), Periodo[Ano] = SELECTEDVALUE(Periodo[Ano]), Periodo[Mês] <= UltimoMes)

------
I've trying a couple things using SUMX, but nothing is working.

Thanks in advance!

[Anonymous · Accepted solution]

Hi @geisla_melo ,

As far as I know, this is a common problem with Power BI measure, the value of the total row is not calculated correctly because of the context and the calculation logic in the measure, you can try to create a new measure and use the Sumx() function to calculate the correct total value by defining a virtual table, for your current measure you can For your current measure you can try to create a new measure similar to this one and replace the corresponding measure in visual to achieve the requirement.

CH Mês Anterior1=
Var _table=Summarize(CargaHorariaAluno,[Mês], "1",[CH Mês Anterior])
return
If(Hasonevalue(CargaHorariaAluno[Mês]), [CH Mês Anterior], Sumx(_table,[1])

You can try a similar measure logic approach to achieve the correct display of the total column, if not, please refer to this blog that explains the same problem and solution.

Fixing Incorrect Totals Using DAX Measures In Power BI | Enterprise DNA

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.