Forum Discussion

SaaM's avatar
SaaM
Icon for Helper II rankHelper II
9 months ago
Solved

DAX total != total rows

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 t...
DAX
Row
sum
total
  • DataNinja777's avatar
    DataNinja777
    9 months ago

    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,

Zanqueta's avatar
Zanqueta
Icon for Super User rankSuper User
9 months ago

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's avatar
SaaM
Icon for Helper II rankHelper II
9 months ago

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's avatar
    DataNinja777
    Icon for Super User rankSuper User
    9 months ago

    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's avatar
      SaaM
      Icon for Helper II rankHelper II
      9 months ago

      Thank you so much DataNinja777 , your solution works !