Sumover avoiding duplicates

Question & Answer
, ,
1

Hello,
I have the below table (results of two joint data sources) and I want to calculate a unique value of EU Stock. The result should be 5442 (1458+1511+975+897+601) for all lines (also when I remove the “month” and “Instock/Out of stock” fields). I have already created a helper column “CountOverUnits” to count the duplicates.

Product Country Month Instock/Out of stock Local Units CountOverUnits
Product A 1 Instock 3
Product A 2 Instock 3
Product A 3 Instock 3
Product A DE 1 Instock 1458 3
Product A DE 2 Instock 1458 3
Product A DE 3 Instock 1458 3
Product A ES 1 Instock 1511 3
Product A ES 2 Instock 1511 3
Product A ES 3 Instock 1511 3
Product A FR 1 Instock 975 3
Product A FR 2 Instock 975 3
Product A FR 3 Instock 975 3
Product A IT 1 Instock 897 4
Product A IT 1 OOS 897 4
Product A IT 2 Instock 897 4
Product A IT 3 Instock 897 4
Product A UK 1 Instock 601 4
Product A UK 1 OOS 601 4
Product A UK 2 Instock 601 4
Product A UK 3 Instock 601 4
2

Can you take the sum over Local units and divide by the avg count over units? Something like this for the count units might work.

avg(countOver({client_id[users]},[],PRE_AGG))
3

Hi @formatoa , We hope the response from @Max helped you. Let us know if this is resolved. And if it is, please help the community by marking the answer as a “Solution”.

Many thanks.