Calculating Total According to Unique Key

[Anonymous]

Hi All,

I need help with creating an expression to sum values for a given field with the ability to filter according to a distinct key value to avoid double counting.

For example

Key	Sale
Key1	500
Key1	500
Key2	1000
Key2	1000
Key3	1500
Key3	1500
Key3	1500

I want to calculate distinct Key1 + Key2 + Key3 and arrive at 3000 as the total instead of 7500.

Your help will be greatly appreciated

p.s. if you're wondering the kind of data set that has this set-up, it is list of flights transporting cargo under a unique contractual agreement, where the contractual agreement is the Master Air Waybill.

[sunny_talwar]

Try this

Sum(Aggr(Sale, Key))

or this

Sum(Aggr(Avg(Sale), Key))

or

Sum(Aggr(Sum(DISTINCT Sale), Key))