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

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

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:

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:

https://public.tableau.com/views/ExampleMultiTouchAmount/Summary?:embed=y&:display_count=yes&:showTabs=y

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...

• ###### 1. Re: Summing Opportunity amount with multi campaign attribution (With LOD?)

Hi Frederic!

I do not have to access to Tableau at the moment, but I think this should work:

{ FIXED [Parent Campaign], [Stage], [Opportunity SID], [Contact SID] : AVG([Amount]) }

• ###### 2. Re: Summing Opportunity amount with multi campaign attribution (With LOD?)

I tested your formula and got to \$70...

Thanks for contributing anyways...

• ###### 3. Re: Summing Opportunity amount with multi campaign attribution (With LOD?)

With the help of the always reliable Joe Mako, I got to the right formula:

{fixed [Opportunity SID]:avg(if [Stage]='8: Won' then [Amount] end)}

I updated the workbook from the original post with Joe's solution, which shows \$32...