Please help with a simple metric. In the table, the sum of the metrics shows correctly. On the Waterfall and similar charts, the sum is completely different and it is the same metric.
Contribution wrong waterfall.pbix (112.3 KB)
@Harris
This is a classic “Totals Don’t Add Up” problem.
When you are seeing the correct value at the row, but the incorrect value at the total, it has to do with the fact the aggregation is occurring before the critical multiplication, or whatever else you are attempting to evaluate.
This is because in Power BI each row of a table has a specific set of filters ensuring that the calculation is performed at the correct granularity, but once you get to the total row you are looking at the same calculation in the absence of filters.
That is a fundamental difference between Excel and Power BI.
In Excel, Total = the aggregation of the values above
In Power BI, Total = when the above filters are removed, what is the aggregation.
Now that we know why this is occurring, how do we fix it?
The simple answer is we need to force Power BI to do the multiplication one row at a time, and then add up the results to evaluate the total.
To do this we need to use SUMX().
If we load the data into Excel, we can see that the Total row in the table should be returning 8.08%
Now we can see that the Total in the Waterfall Chart is correct, but the Total in the Table is wrong.
To fix it, we simply need to force the row-by-row calculation using SUMX()
Now we get the same grand total as we see in the Waterfall chart
NOTE: When using SUMX(), you should always work through the smallest possible table, in this case Assortment has 15 rows, and Sales has over 4,300 rows, so it is significantly more efficient to step through the Assortment table.
Contribution wrong waterfall.pbix (113.2 KB)
1 Like
Thank you so much for the very detailed answer @JasonCockington 
Hello @Harris, if the inquiry was answered kindly mark the response that you think best helped you. Thank you so much.
Hi @Harris, we’ve noticed that no response has been received from you since a few days ago.
We just want to check if you still need further help with this post?
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Harris, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!