Calculating total not sum of rows

[richnorris]

We have a series of trades, each with a volume associated.

It might be the case that two salespeople are allocated to that trade, but in the dashboard we want to show the full volume against both salespeople.

When we come to total, however, we dont wish to double count.

Cant seem to wrap my head around how to go about doing this, everything I come up with would work for either the rows or the total but not both.

All the data is on the same table, and must remain that way. Thanks in advance for any suggestions!

[#2]

Hi Rich,

Can you send me your desired result example in MS-EXCEL Format.

So that we can work on that.....

Regards,

Dushyant

[#3]

You can use Set Analisys....

=sum({1} SALES) -> to show a total

=sum({$<PEOPLE={YOUR CONDITION} >} Sales) ->To show specific salespeople

[johnw · Accepted solution]

So you have a data model like this?

Salesperson, Sale, Amount
Ann, 1, 500
Bob, 1, 500
Charlie, 2, 1000
Charlie, 3, 1500

And you want to see this?

Salesperson Amount
Ann 500
Bob 500
Charlie 2500
Total 3000

Well, my first question is WHY must your data model remain that way, because normalizing the sale information would fix your problem. I'd model it like this:

Salesperson, Sale
Ann, 1
Bob, 1
Charlie, 2
Charlie, 3

Sale, Amount
1, 500
2, 1000
3, 1500

And that data model gives you the right result by just using sum(Amount). Still, as long as you have an ID for the sale like I've indicated, so that you can detect the duplicates, this would work:

if(dimensionality()=0,sum(aggr(Amount,Sale)),sum(Amount))

This says, "If I'm on the total row, then sum up the amounts, but only once for each sale. If I'm not on the total row, then just sum up the amounts."

I tried coming up with an expression without the IF that would work, and I'm betting there is one, but I couldn't figure it out. Dimensionality is an easy fix.