Calculate sum of DISTINCT field value based on values of another columns

[andymanu]

Hi All,

Please find the attached sample data set for this question below;

	Sales Person
Product_ID	Product_Name	Region	Sales	Sales Manager	Sales Person 1	Sales Person 2	Sales Person 3	Sales Person 4
P10	Apple	North	30000	Adam	Siyara	Adam
P11	Nokia	South	25000	Ben	Don
P12	Samsung	East	35000	Don	David	Don
P10	Apple	South	26000	Adam	Tess
P12	Samsung	East	30000	Ben	David	Don

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.

Desired output should be something like below;

Product_ID	Product_Name	Region	Sales Person	Sales
P10	Apple	North	Adam	30000
P10	Apple	North	Siyara	30000
P10	Apple	North	Dian	26000
P10	Apple	South	Tess	26000
P11	Nokia	South	Ben	25000
P11	Nokia	South	Don	25000
P12	Samsung	East	Don	65000
P12	Samsung	East	David	65000
P12	Samsung	East	Ben	30000

Could someone please help me with this issues?

Thank you in advance.

Kind regards,

Andy

[andymanu]

I assume something like below should work,

1. Converting the straight table in to a cross table,

2. Use a pivot table to visualize the data,

3.  Use an expression like,

=Sum(Aggr(Sum(DISTINCT Sales), [Sales Person], Product_ID, Region))

Appreciate your feedback.

Thank you.

Kind regards,

Andy