I need help with one of the problem I am trying to solve.
I have a pivot table pasted below:-
I have converted absolute revenue brought by each sales person brought within each discount percent interval(per int) into relative percentage using following formula:-
([revenue]) / Sum(total <[sales person]> [revenue]
Now I am trying to do peer comparison and for that I have to subtract Total from each column i.e Total - A, Total -B and so on.. so my Total should remain fixed. I tried using following code but I am not getting right values:-
Sum([revenue]) / Sum(total <[Sales Person]> [Revenue]) - (Sum([revenue]) / Sum(All <[Per Int]> [revenue]))
I was wondering if anybody have seen with this kind of situation.