Sum Values based on duplicate unique values in another column

Reply
Highlighted
Yellow Belt

Sum Values based on duplicate unique values in another column

Afternoon all, 

 

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.

 

Example:

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.

 

MemberCampaignOppty ID AmountActual
JohnCamp1123a$1,000.00$1,000.00
JohnCamp2123a$1,000.00
JaneCamp374k1$1,000.00$1,000.00
JosieCamp484kd$1,000.00$1,000.00
SamCamp5923j$1,000.00$1,000.00
Total  $5,000.00$4,000.00

 

Hope this makes sense, any help is appreciated, thank you!

Highlighted
Yellow Belt

added note, when I create my bar graph as a stack with the unique opportunity ID as a series across created dates for the quarter, then the correct totals per date range will be there. 

Highlighted
Black Belt

For ease of use, I would restructure the data

Such that I have

data1 = one row per campaign.

data2 = one row per opportunity

 

then APPEND them instead of JOIN them.  By JOIN'ing the data you are currently spreading Opportunity Amount across each associated campaign (hence why you're trying to do 'crazy math'.  If you APPEND the data, then you don't have to worry about crazy math and can instead take easy sum or division.

 

In this video https://www.youtube.com/watch?v=I3y-LSch-hM&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=2&t=5s I describe how to build a STACK'ed dataset (my name for datasets that have been UNIONed.)  Instead of forecasts and actuals, you'd just append opptys and campaigns.


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
How to create #forecast in #Domo using #WindowFunctions in #BeastModes and a little #MagicETL ##### CONSULTING SERVICES #### I have left Domo to start my own...
Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.