Complicated Sum Field

[picnichouse]

Hi,

I'm trying to run a report showing sales people's commissions on different orders, sorted by the sales person. Sometimes the sales person gets multiple commissions on the same order (different types of commissions: primary account holder, referral, etc).

I have a subsummary in the report showing the total commission amount for each sales person. I also want to show the total revenue of the sales involved, but I can't simply sum the revenue field because it would count the revenue for each commission, including commissions on the same order, thereby doubling the revenue.

So I need to create a field that makes sure it only sums the revenue once for each order.

Anyone up for the challenge? I've attached a file that lays out the problem exactly.

Thanks!

ordertest.fp7.zip