Compare vs same sales

[Pablo_MC]

Hi everyone and thanks in advance. 

I have a complicated requirement that i can't achieve, i hope you can help me. 

I have this data set:

Team	Product	Sales
A	Oranges	60
A	Apples	10
A	Bananas	20
B	Oranges	30
B	Strawberries	10
B	watermelons	20
C	Apples	100
C	watermelons	50
D	Strawberries	70

I want to compare each team with the average of the teams but only comparing the products that it sell. 

For example, team D only sell strawberries, so the average they want to compare is = B(but only sales from strawberries) and D (all of its sales) .

Final result that i want to achieve:

	Average sales	Average of teams selling same products
A	30	40	AVG Sells from A (oranges, apples, bananas) , B(oranges) , C (apples, banana)
B	20	50	AVG Sells from B (oranges, strawberries, watermelon) ,A(oranges) , C (watermelon), D ( strawberry)
C	75	36,66666667	AVG sells from C (apples, watermelon, banana), A (apples, banana), B(Watermelon)
D	70	40	AVG sells from D, B (strawberries)

Case of team A in numbers is avg(avg(60;10;20);30;avg(100;20)). 

Can i achieve this on set analysis ? 

Thanks!

[Thiago_Justen_]

Hello my friend. What's up?

I guess your output isn't correct based on the data you provide us. See that Team C didn't sell banana.

Anyway, I got the output below. I hope it gets you some insights.

The script I've used is attached.

Feel free to contact me.

Take care 😉

[Pablo_MC]

Thank you Thiago.

Yes, i've made a mistake in the output, C didn't sell banana.

Your solution it's ok but i need to do it in expressions and not in script. I have several fields that users can filter on it and i can't have the value loaded in script.

Thanks!