Expressions for Pivots (avoiding double counting)

[Anonymous]

Here is example of the data I am working with. There are two challenges with my pivot of this data.

1) 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 as shown below (i.e. within program 1, I want UMASS $ to be counted once in the pivot AND I want to allocate the UMASS totaL $ deal between 2 campaigns equally (500 for program 1 and 500 for program 2).

Output I want:

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

[sunny_talwar]

Try 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]))