Sum with exclude and include using set analysis

[kennethand]

I have a datafile looking like this (just a snip):

ID	Weight	Product
1	1,29	A
1	1,29	B
2	0,64	A
3	2,65	A
3	2,65	B
3	2,65	C
4	0,50	C
5	1,00	A
5	1,00	C
6	1,02	A
7	0,64	A
7	0,64	B
7	0,64	C
8	0,76	A
9	1,29	A
9	1,29	B
9	1,29	C
10	0,72	A
11	1,02	C
12	0,86	A
12	0,86	C

Now I like to calculate how many percent has choosed Product=A but not B and C.

I have to sum the weight but only distinct ID and then divide with the total sum of total weight (also distinct iD). I know to measure the last part:

sum(aggr(sum(DISTINCT Weight), [ID]))

But how do I calculate how many has choosed Product=A but not B and C?

In this example only ID 8 and 10 has choosed A and not B and C and their sum of weight is 1.48 (0.76+0.72). So the the precent will be 12% (1.48 / 12.37).

[y_grynechko · Accepted solution]

Hey, E() allows you to exclude the ones that never had B or C assigned.