Sum Over distinct fields

Question & Answer
, , ,
1

I want to sum over the countries, how many items I have (considering I have duplicates as below):

Country Site Items
FR DBG2 12
FR DBG2 12
FR DBY2 105
FR DBY8 35
FR DBY8 35
IT DAH1 51
IT DAH1 51
IT DAH1 51
IT DAH1 51
ES BCN3
ES DBE2
DE DBI4
DE DBI7
UK DBW1
UK DBX8

My ultimate goal is to arrive to a pivot containing the following:

Country Items
FR 152
IT 51
ES 0
DE 0
UK 0

So far, doing “sum(Items)/count({Site})” I’m getting the total items per site and then, doing the pivot and adding the total, I’m getting the total per country (but I have to add all the sites and is what I want to avoid).

Thanks in advanced for the support.

2

Hi @galaugus,

Can you try this?

sum(min(Items, [Site]))

You can take a look at use case #2 in this article to understand what the calculated field is doing.

3

Thanks David. Indeed it has worked. And thanks for sharing the article