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,

[vaibhavmahajan]

Hi @SaaM,

I hope you are doing well today 🙂❤️

There are two solutions you can use to fix the totals issue in your table visual.

Both ensure the total equals the sum of the row values:

Solution 1: Using SUMX with IF

Calcul_incremental :=
SUMX (
    VALUES ( YourTable[YourRowColumn] ),
    VAR Diff = SUM ( YourTable[CA N] ) - SUM ( YourTable[CA N-1] )                  
    RETURN
        IF ( Diff > 0, Diff, 0 )
)

• Evaluates row by row
• Applies the condition per row
• Totals now match the sum of rows

Solution 2: Using SUMX with MAX (shorter)

Calcul_incremental :=
SUMX (
VALUES ( YourTable[YourRowColumn] ),
MAX ( SUM ( YourTable[CA N] ) - SUM ( YourTable[CA N-1] ), 0 )
)                          

• Shorter, cleaner syntax
• Works exactly the same
• Ensures totals equal the sum of rows

Work Notes:

• Replace YourTable[YourRowColumn] with the column that defines each row in your table
• e.g., Customer, Account, Product
• Both approaches work for table, matrix, and card visuals

If this answer helped, kindly give Kudos and mark it as the Accepted Solution

to help other members find it more quickly.

Best regards,

Vaibhav Mahajan

LinkedIn: https://www.linkedin.com/in/vaibhavnmahajan

[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 !