8 Replies Latest reply: Jun 10, 2016 2:03 PM by Saran De

# Sum for distinct values

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?

• ###### Re: Sum for distinct values

May be this:

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

or

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

or

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

• ###### Re: Sum for distinct values

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

• ###### Re: Sum for distinct values

Check the attached or share a sample of yours

• ###### Re: Sum for distinct values

One more expression on Sunny's App..

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

• ###### Re: Sum for distinct values

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.

• ###### Re: Sum for distinct values

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.

• ###### Re: Sum for distinct values

Hi,

=Sum(Amount)

cant you just use expression:

=Sum(DISTINCT Amount)

That will only total the unique Amount Values.

Bri.

• ###### Re: Sum for distinct values

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