Sum of distinct

[jduenyas]

Hi all

I have this sort of table:

QuoteNumber	QuoteValue	OrderNumber	OrderValue
1000	500
1001	350	5001	250
1002	750	5002	500
1001	350	5003	500
1003	600	5004	400
1004	300
1002	750	5005	350
1005	1000	5006	1000

Quotes and orders. Some quotes do not have orders, others have orders for the same value of the quote or more (or less) and some quotes have multiple orders.

I wish to sum the total of distinct quotes only. So for instance quote number 1001 and 1002 should be totaled only once. The total of column QuoteValue should then be 3500 (not 4600)

I then want to divide the total of the OrdersValue by the total of the QuotesValues to get the rate of return (win ratio) of my quotes.

Your help is appreciated

Thanks

Josh

[maxgro]

Try the attachments

[jduenyas]

Thank you Massimo but I am afraid this is not correct because it will sum on the distinct VALUE of the quote and not on the distinct quote. So if there is another quote, lets say 1006 with value of 600 and it had an order 5007 for 600, it will not be added to the total of distinct QUOTES

Quote number	Quote Value	OrderNumber	OrderValue
1000	500
1001	350	5001	250
1002	750	5002	500
1001	350	5003	500
1003	600	5004	400
1004	300
1002	750	5005	350
1005	1000	5006	1000
1006	600	5007	600

Total of distinct QUOTES should be now 4100 but your calculation will still be 3500

Thanks

[albertovarela · Accepted solution]

Hello,

Try this for to sum distinct quotes ~ QuotesValue:

=sum(aggr(distinct QuoteValue,QuoteNumber))

This for OrdersValues:

=sum(OrderValue)

and finally, for the rate of return:

sum(OrderValue) / sum(aggr(distinct QuoteValue,QuoteNumber))

Hope this helps...

[jduenyas]

Thanks

That did it.