DAX total != total rows

[SaaM]

Hi, 

I have an issue where the total of the rows is not equal to the total calculated in a table, let me explain what I mean: 

I calculate first the difference between the CA N and CA N-1 that I store in diff_CA measure
and I store in Calcul_incremental measure:

- if (diff_CA >0, diff_CA, 0)

and I except the total to be 129 367,62 +0,00 = 129 637.62 and not 129 367.62 -263.86 = 129 103.76

What am I doing wrong here to fix the total taking into account not the values calculated with the condition ? 

Thank you in advance
Kind regards,

[Rufyda]

I'm glad you found a solution! We're here in the community to support each other.

Regards,
Rufyda Rahma | MIE

[Zanqueta]

Hi @SaaM,

This behaviour is expected because of how DAX calculates totals: the total is not the sum of the row results, but a recalculation of the measure in the total context (all rows combined). In your case:

• For each row:
  Calcul_incremental = IF(diff_CA > 0, diff_CA, 0)
  works correctly because diff_CA is evaluated per row.
• For the total:
  The same formula runs in the grand total context, where diff_CA is aggregated across all rows first. If the combined diff_CA is negative, the condition fails, and the total shows a smaller value.

Please, try something like this:

Calcul_incremental =
SUMX(
    VALUES('YourTable'[YourGroupingColumn]),
    IF([diff_CA] > 0, [diff_CA], 0)
)

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[SaaM]

unfortunately it is not as I don't get the result expected using the formula suggested: 

How should I proceed to have the total that equals 0.00 + 129367.62 

Thanks
Kind regards, 
SaaM

[DataNinja777 · Accepted solution]

Hi @SaaM ,

It is likely that your total doesn't work because you have multiple columns which is affecting your filter condition.  In order for the sumx solution to work you will need to incorporate all the filter condition columns into your sumx formula.  An example of the formula is as shown below:

Calcul_incremental_Total = 
SUMX(
    SUMMARIZE(
        'YourTable', 
        'YourTable'[Column1], 
        'YourTable'[Column2]
    ),
    IF([diff_CA] > 0, [diff_CA], 0)
)

 Would you like me to generate the exact dax formula to fix the total? If so, please let me know which columns are currently listed in the rows section of your table visual.

Best regards,

[SaaM]

Thank you so much @DataNinja777 , your solution works !