Sum for distinct values

[#1]

Hi all,
I have a dataset similar to below:

Trip Id     Amount

a              10

b              10

b              10

b              10

c              5

I want sum(amount) for distinct trip ids which should be: 25(10+10+5)
is aggr() an option?

Any solutions?
Thanks in advance

[sunny_talwar]

May be this:

Sum(Aggr(Only(Amount), [Trip Id]))

or

Sum(Aggr(Avg(Amount), [Trip Id]))

or

Sum(Aggr(Sum(DISTINCT Amount), [Trip Id]))

[swuehl]

Consider changing your data model in a way your fact table only shows a single record per fact value, i.e. try to avoid data duplication caused by e.g. joining tables.

To Join or not to Join

Don't join - use Applymap instead

Then a simple Sum(Amount) should return what you want.

It's better to spent the time on data modelling than fixing things later in (more complex) expressions.

[#4]

Thanks for the reply! Will all of them lead to same result? I checked for the first 2, it doesn't

[#5]

Thanks Swuehl!
However, there are no overall duplicate rows. There are other columns(not mentioned here)  which make the entire row unique. I don't want to create a separate table just for a distinct sum. 

[sunny_talwar]

Check the attached or share a sample of yours

[#7]

Hi,

Instead of using expression:

=Sum(Amount)

cant you just use expression:

=Sum(DISTINCT Amount)

That will only total the unique Amount Values.

Bri.

[#8]

Hi,
That would give the result as 15(according to the data set in my question). Two different ids can have same amount.

[Saravanan_Desingh]

One more expression on Sunny's App..

=Sum(Aggr(FirstSortedValue(Amount,Key),[Trip Id]))