3 Replies Latest reply on Jan 27, 2016 10:22 AM by Frederic Pinchon

    Summing Opportunity amount with multi campaign attribution (With LOD?)

    Frederic Pinchon

      In a CRM context, I have a data set from a multi campaign attribution model where I am getting possibly multiple contacts attached to the same opportunity, each possibly touching more than 1 marketing campaign, and each opportunity present at multiple stages. data source would look like that:

      Dataset example.png

      As you can see, Green contact C167881 is touching 5 campaigns and has a role on Opportunity O118934.

      I need to build a dashboard where I report the amount influenced by each campaign, and am trying to figure out which formula would let me present a total amount per Parent Campaign, with a breakdown by Campaign, WITHOUT DOUBLE COUNTING. For instance, for PC5, I should get to 3+7+9+2+11= $32 of influenced $ Won, highlighted  in yellow. Here is what I came up with:


      You can download the workbook from here:



      I am able to count properly the # of deals, using this formula for Won Deals:

      countd(if [Stage]='8: Won' then [Opportunity SID] end)


      But for Won Amount, I can't get to $32, despite this series of failed attempts:

      1. if [Stage]='8: Won' then {avg({fixed [Campaign SID],[Opportunity SID],[Contact SID]:avg([Amount])})} else 0 end
      2. sum(if [Stage]='8: Won' then {avg([Amount])} else 0 end)
      3. if [Stage]='8: Won' then {include [Parent Campaign ID],[Opportunity SID],[Contact SID]:MIN([Amount])} else 0 end
      4. {include [Opportunity SID]:MIN([Amount])}
      5. sum(if [Stage]='8: Won' then [Amount] else 0 end)
      6. sum(if [Stage]='8: Won' then {include [Opportunity SID]:sum([Amount])} /{include [Opportunity SID],[Contact SID]:countd([Contact SID])} else 0 end)
      7. sum(if [Stage]='8: Won' then {include [Campaign SID],[Opportunity SID]:min([Amount])} else 0 end)
      8. sum(if [Stage]='8: Won' then {fixed [Opportunity SID]:avg([Amount])} else 0 end)
      9. sum(if [Stage]='8: Won' then {exclude [Contact SID]:avg([Amount])} else 0 end)


      Would anybody know which formula could get me to the $32?

      I suspect there must be a LOD for that...