Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this:
Sum(Aggr(Only(Amount), [Trip Id]))
or
Sum(Aggr(Avg(Amount), [Trip Id]))
or
Sum(Aggr(Sum(DISTINCT Amount), [Trip Id]))
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.
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.
Thanks for the reply! Will all of them lead to same result? I checked for the first 2, it doesn't
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.
Check the attached or share a sample of yours
Hi,
Instead of using expression:
=Sum(Amount)
cant you just use expression:
=Sum(DISTINCT Amount)
That will only total the unique Amount Values.
Bri.
Hi,
That would give the result as 15(according to the data set in my question). Two different ids can have same amount.
One more expression on Sunny's App..
=Sum(Aggr(FirstSortedValue(Amount,Key),[Trip Id]))