I am trying to do a sum calculation but the figure is not showing to what it should be, I want it to be £1, 612, 536.42 but it showing as £1, 414, 607.77, I tried to do the formula in the text box underneath "Total RRP CORRECT" but it does not seem to work? Also will I need to change the different charts with the new formula as they are using seperate dimension when clicking on the relevant buttons on the side?
I wonder about the DISTINCT in your formula. What is the reason for it? A Sum(Distinct...) will only sum up a certain value once and that may be the reason why you see a different total.
Example:
Cust A 5
Cust B 5
You would expect a total of 10, but as they have both the same value, it will be summed only once and return a total of 5. I hope that helps to give you the right idea...
I have sorted the formula now, but something wierd is happening! When I click on Person 131 it shows the following figures in the Straight table which are correct as :
Total RRP - £648,703.79
Total Mar - £38,257.82
Total Comm - £86.19
% Diff - 0.23%
But when I have no filter on, Person 131 is showing the incorrect figures as
yes, summing up the sales reps makes 15m. The 1.6m comes from a DISTINCT on RRP and as explained before, that does not sum up double values. I saw some of the reps have the same result, but those won't be summed up as long as you have a DISTINCT with the value field.
