Sum using distinct

[#1]

Hi,

This is a snip of my data:

I need to sum "Crop" and in this example my choice is 'Cereal'. I need to look at the ID and Spieces as well.

ID 1 has one field with the size 10 (Cereal + Spring Barley) and one field with the size 12 (Cereal + Winter barley). This total field size is then 22 for Cereal.

ID 2 has one field with the size 10 (Cereal + Winter wheat) and one field with the size 15 (Cereal + Oat) and one field with the size 20 (Cereal + oat) and finally one field with the size 20 (Cereal + Winter wheat). his total field size is then 67 for Cereal.

All in all it's 87 for Cereal.

How does the syntax for a new measure looks ?

Cheers,

Kenneth

[Gysbert_Wassenaar]

Try sum(aggr(sum({<Crop={'Cereal'}>} distinct Fieldsize),Crop,Spieces))

[#3]

Thanks but I think I need to take the ID into consideraton as well ?

[Gysbert_Wassenaar · Accepted solution]

That's not necessary in this case, but if you want you can add the ID field to the field list in the aggr function:

=sum(aggr(sum({<Crop={'Cereal'}>} distinct Fieldsize),Crop,Spieces,ID))