Calculate sum of DISTINCT field value based on values of another columns
Please find the attached sample data set for this question below;
Sales Person 1
Sales Person 2
Sales Person 3
Sales Person 4
My requirement is, I want to calculate total sales performed by each Sales Person (Including the Sales Manager). for each product. However, in my data set the there are instances where the Sales Manager is duplicated as a Sales Person (for example, P10 in region "North" where the Sales Manager is duplicated into the Sales Person 2 column) and when calculating the individual sales by the Sales Person, I don't want to calculate the sales value for Adam as $ 60000 instead, it should be $30000.
I tried converting the data set in to a cross table but couldn't get the desired result. Assume, I have to use the "Aggr" function but not so sure how to use it.