Power BI TOTALS may produce incorrect results when working with NON-ADDITIVE DAX measures
Problems with non-additive DAX calculations
✅When looking at a matrix/table in a Power BI report, the TOTAL corresponds to the SUM of the individual rows. But the TOTAL is the SUM of the rows only for ADDITIVE measures.
⛔The complexity appears when working with NON-ADDITIVE measures, especially when Power BI returns incorrect totals.
This is a very complex subject, which Alebrto Ferrari from SQLBI has explained very well with detailed examples in his article.
👉The solution is to refine the measure, check the requirements according to the context calculation and force the aggregation in the DAX measure according to each iteration.
Problems with non-additive DAX calculations:
A first example with NON-ADDITIVE measure: Count Products
Working with NON-ADDITIVE calculations, the SUM results can be wrong:
✅Sales Amount is additive measure, it is computed over any dimension (in my case for each Store Name)
⛔Count Products is NON-ADDITIVE measure: it does not return the SUM of individual rows at the Total level
To fix this, I created a SUMX measure, to force additivity over the Store Name, and this helped me correct the wrong total:
A second example with NON-ADDITIVE measure: Average Products
Averaging individual rows also produces incorrect results results in the Total level, as we need to obtain the average of an average.
The solution is to add an iteration on the Store Name in the measure, as I did previously with SUMX products.
👉👉 When a measure is non-additive, the solution is to specify at which granularity the measure should be calculated and then perform an iteration using iterator DAX functions at the correct granularity, summing up the values at the end.