Have you ever gotten yourself in a situation when your calculation works great on a row by row basis, but when you look at a total, it does not match to the SUM of the individual rows?
If so, the problem is not Power BI, the problem is in your calc. The good news is that it’s usually very simple to fix this issue. I go over a possible way to fix it in the video below:
Here is the data set for the video:
Date |
Customer |
Sales |
Margin |
Rep |
||
1/1/20 |
A |
100 |
10 |
John |
||
1/1/20 |
B |
200 |
50 |
Tom |
Here are all the calculations that I used in the video:
Sales = SUM(Sales[Sales])
Margin = SUM(Sales[Margin])
Margin % = DIVIDE([Margin], [Sales])
Commission = IF([Margin %]>.15, .10*[Sales], .05*[Sales])
Fixed Commision = SUMX(VALUES(Sales[Rep]), [Commission])