Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table like this
Amount 1 | Amount 2 | ID |
9500 | 0 | 123 |
9500 | 0 | 123 |
9000 | 0 | 456 |
0 | 1000 | 678 |
0 | 1000 | 678 |
If Amount 1 is not null, I need to get a summation of a group by the ID.
If Amount 1 is not null, I need to get a summation of a group by the ID.
I am trying to do something like this:
=Sum(
If( Is NotNull(Amount1), aggr(sum(Num(Amount1, '$#,##0.00')
), [ID]),
If( Is NotNull(Amount2), aggr(sum(Num(Amount2, '$#,##0.00')
), [ID])
)
but it is giving me incorrect data returned. Any suggestions?
The result set I want is 123 to sum to 9500, 456 to sum to 9000 and 678 to sum to 1000. Note, I have tried using a distinct and it did not seem to impact the results as desired.
It doesn't seem like you're looking for a sum - you seem to be describing picking a single value rather than summing them.
Given there only ever seems to be a value in one field or the other, you could just use
Sum(Amount1) + Sum(Amount2)
if you wanted to actually sum. However, since I don't think you're summing, perhaps:
alt(Only(Amount1),0) + Alt(Only(Amount2),0)