Forum Discussion

geisla_melo's avatar
geisla_melo
New Member
3 years ago
Solved

Total Column doesn't match

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!
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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. 

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.