Avoiding double counting in pivots

[Anonymous]

Here is a simplified version of the type of data I work with.  As you can see, it shows by marketing campaign which sales reps were involved, which deals were won, and how big each deal was.  

Name of Marketing Campaign	Sales rep	Name of Deal	Dollars from each deal
Program1	John	UMASS	$ 1,000
Program1	Drake	BW	$ 3,500
Program1	Jim	TS	$ 1,500
Program2	Blake	UMASS	$ 1,000
Program2	Sofie	bbc	$ 1,000

I am trying to pivot this data but here is my challenge.

The deal called UMASS (worth $1000) appears in both programs because both marketing programs helped with it. What I want to do is allocate equal $ to program 1 and 2 for this deal (in other words, I want to assign 500 for program 1 and 500 for program 2 for this UMASS deal)instead of summing all the deals by campaign (which would result in double counting $). Ultimately I want to pivot by program so that the totals are shown like this. How do I do this in a pivot? Thanks so much.

Name of Marketing Campaign	total dollars from deals
Program1	5500
Program2	1500
Grand Total	7000

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(Sum([Dollars from each deal])/Count(TOTAL <[Name of Deal]> [Name of Deal]), [Name of Marketing Campaign], [Name of Deal]))

[Anonymous]

Actually, I just realized that my data is a little more challenging than I originally thought. Here is a better example. There are two problems:

1) As before, the same deal (UMASS appears in multiple distinct campaigns)

2) Within the same campaign UMASS appears twice.

Name of Marketing Campaign	Sales rep	Name of Deal	Dollars from each deal
Program1	John	UMASS	$1,000
Program1	Drake	BW	$3,500
Program1	Jim	TS	$1,500
Program1	Blake	UMASS	$1,000
Program2	Ivan	UMASS	$1,000
Program2	Sofia	BBC	$1,000

Despite the two constraints above, I want the output of the pivot to be the same as before (i.e. within program 1, I want UMASS to be counted once in the pivot AND I want to allocate the UMASS deal between 2 campaigns equally (500 for program 1 and 500 for program 2). Have been struggling with this all day to no avail.

Name of Marketing Campaign	total dollars from deals
Program1	5500
Program2	1500
Grand Total	7000

[sunny_talwar]

May be this

Sum(Aggr(Sum(DISTINCT [Dollars from each deal])/Count(DISTINCT TOTAL <[Name of Deal]> [Name of Marketing Campaign]), [Name of Marketing Campaign], [Name of Deal]))

[Anonymous]

Thank you but something is wrong with the count portion. it ends up counting all distinct marketing programs (2 in this case) regardless of which campaigns helped with each deal. So I have over 4k marketing programs, and that's what the denominator is for every campaign. hmmm

[sunny_talwar]

Would you be able to elaborate with an example?

[Anonymous]

Sure but before I do, I wanted to make sure I am explaining everything correctly. The problem I am having is with this portion of the formula: Count(DISTINCT TOTAL <[Name of Deal]> [Name of Marketing Campaign]

It is meant to count how many distinct campaigns contributed to a deal but it ends up doing something else. It looks like it ends up giving the distinct count of marketing campaigns overall regardless of what deal we are looking at. SO my dataset has over 4k campaigns and this expression when used by itself gives me exactly that. Instead it should just count the number of distinct campaigns that contributed to each deal. Does that make sense?

[sunny_talwar]

But the reason we use TOTAL <[Name of Deal]> is to tell the expression to summ the campaign for each deal.... not sure why you get all... again... unless I see it... it might be difficult to understand the problem completely

[Anonymous]

you are correct, as usual. I think I got it to work. User error.

[sunny_talwar]

Super awesome