using Aggr to sum distinct values

[#1]

I need to total a price column on orders as follows:

id	salesman	price
1	tom	100
1	jim	100
2	jim	1
2	sam	1
3	tom	100
3	bob	100
3	joe	100
		201

ie when totalling we should only sum the price once per order. I tried using:

sum(aggr(price,id)), which does the correct sum but it look like this:

id	salesman	price
1	tom	100
1	jim	0
2	jim	1
2	sam	0
3	tom	100
3	bob	0
3	joe	0
		201

The sum is correct but I dont want it to zero out the prices like that. Any ideas what formula i can use to make it look like the first table?

Thanks

[#2]

I think this works, so that aggr is only used in the total and doesnt affect the displayed prices in the table:

if(dimensionality()=0,sum(aggr(price,id)),price)

Anyone got a better solution?

[johnw]

Possibly sum(aggr(nodistinct price, id),price)), but I bet it messes up the total. If so, no, I'm not thinking of a better solution than yours right now.

[vgutkovsky · Accepted solution]

See attached solution.

Regards,

[#5]

Nice one, thanks.