Sum Values based on duplicate unique values in another column
Having a hard time figuring out this one out. I have a tabular report i'm connecting to in salesforce, which is a "Campaigns with Influenced Opportunities" report. The report shows a line item for all campaigns that contain a member with an opportunity. The member can be associated to 1 opportunity, but be a member of 3 campaigns, which means that opportunity is listed in 3 line items (Member A, Opportunity 1, Campaign Alpha,beta,zeta)... The opportunity ID is unique, so for every line item with the same opportunity ID i'd like to return the opportunity Amount only once (instead of 3 times).
my answer should be:
Opportunity Total = Sum(Unique ID("Opportunity Amount")) / Sum(Count of that ID(("Opportunity ID"))
however, it must be done at the individual oppty level. If you sum all the oppty amount, then divide by count of ID's, it's wrong because each opportunity can be a different amount. and the right amount needs to be divided by the right count.
4000 = (Sum of amount for Oppty ID for John / Count of Oppty ID for John) + (Sum of amount for oppty id for jane / count of oppty id for Jane) + etc. etc. etc.
Hope this makes sense, any help is appreciated, thank you!