Calculating the Sum of Averages in a Pivot Table

[arveearv]

Hi:

This problem has been nagging at me for several days. There must be a way to do this, but I'm just not seeing it. (I'm using Excel 2007.)

A landscaping company has given me their data. At the beginning of the year, each of their client signs up for one of several pre-paid plans. As work needs to be done, the company sends in a contractor to do the work, then the company pays the contractor. The company wants to measure the profit and loss from each of their customers.

Here's a sample of the raw data. Each row represents a service provided to a customer. Note that each customer's Pre-Paid Plan field gets redundant: every time the customer is listed, it's always the SAME plan amount associated with that client.
Excel 2007

	A	B	C	D	E	F
1	Region	Customer	Pre-Paid Plan Amount	Service Call Date	Service	Contractor Cost
2	East	SIMPSON	$300.00	1/19/2012	Waste Removal	$60.00
3	East	SIMPSON	$300.00	4/30/2012	Edging - small	$100.00
4	East	WEBBER	$550.00	5/15/2012	Watering	$85.00
5	East	WEBBER	$550.00	6/11/2012	Waste Removal	$60.00
6	West	BABCOCK	$550.00	6/7/2012	Planting - Med	$120.00
7	West	ABRAMS	$400.00	1/18/2012	Watering	$85.00
8	West	ABRAMS	$400.00	6/1/2012	Mulching	$80.00

<tbody>
</tbody>

short version​

And here's the ideal pivot table that the company wants to see, but that I'm not able to generate:
Excel 2007

	G	H	I	J
10	Region	Total Pre-Paid Received	Total Contractor Cost	Net Loss/Gain
11	East	$850 (i.e. Simpson-$300; Webber-$550)	$305	$545 (i.e. $850-$305)
12	West	$950 (i.e. Babckock-$550; Abrams-$400)	$285	$665 (i.e. $950-$285)

<tbody>
</tbody>

short version​

The "Total Contractor Cost" makes sense. It's just the total amount that the company has paid out, broken down according to the pivot table definition. The problem is generating the Total Pre-Paid Recieved -- the 2 customers in the East Region have paid $300 and $550. So the income from the East Region should be $850, but when I do this as a Pivot table, that value is $3,050 - the sum of all the pre-paid values every time they appear in the data sheet. Then of course there's the problem of generating that final column, which is the difference between the
totals in Columns H and I.

Any ideas?

 

[Smitty]

Welcome to the Board!

A Pivot Table doesn't have that functionality (although you can do it with PowerPivot), but you can add a field to the data:

Count Unique Items in Excel Pivot Table

HTH,

 

[arveearv]

Wow, Smitty! Thanks.

OK, so from the link you provided, I modified the formula to match my data: (=IF(SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2))>1,0,1). I don't know how it works (yet) but it works, so I have put a marker of "1" in my source data for every first occurrence of a unique value. Great.

So, when I select for the unique values only, I get this pivot table so far:
Excel 2007

	G	H	I	J
22
23		Row Labels	Sum of Pre-Paid Plan Amount
24		East	850
25		West	950
26		Grand Total	1800
27

<tbody>
</tbody>

short version​

Column "I" looks great. And now I want to add columns that are based on all the values (not just the unique ones), as in Column "I" in my ideal pivot table in my original post. Will this be possible in the same pivot table?

Thanks again.

 

[Smitty]

This is probably the closest you would get with a normal pivot table:

Excel 2012

	A	B	C	D
3	Row Labels	Pre-Paid Plan Amount	Contractor Cost	Net Loss/Gain
4	East	$1,700.00	$305.00	$1,395.00
5	SIMPSON	$600.00	$160.00	$440.00
6	WEBBER	$1,100.00	$145.00	$955.00
7	West	$1,350.00	$285.00	$1,065.00
8	ABRAMS	$800.00	$165.00	$635.00
9	BABCOCK	$550.00	$120.00	$430.00
10	Grand Total	$3,050.00	$590.00	$2,460.00

<COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5"><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet4​

With a Calculated Field for Net Loss/Gain. Note that this method wouldn't require an intermediate column in the data.

 

[arveearv]

Hi:

Hmm... It makes sense, but the Pre-Paid Plan column is wrong because it assumes that the customer paid for a full-year plan on EACH contractor visit. We'd need a table that somehow contains Column "I" of the example in my previous post, and Column "C" in your reply above.

What if we generated this table (where "1" represents the first unique occurrence of a customer):
Excel 2007

	A	B	C	D	E	F	G
22
23		Column Labels
24		Sum of Pre-Paid Plan Amount		Sum of Contractor Cost		Total Sum of Pre-Paid Plan Amount	Total Sum of Contractor Cost
25	Row Labels	0	1	0	1
26	East	850	850	160	145	1700	305
27	SIMPSON	300	300	100	60	600	160
28	WEBBER	550	550	60	85	1100	145
29	West	400	950	80	205	1350	285
30	ABRAMS	400	400	80	85	800	165
31	BABCOCK		550		120	550	120
32	Grand Total	1250	1800	240	350	3050	590

<tbody>
</tbody>

AlmostIdeal​

...and could then somehow hide columns "B", "D", "E" and "F"? Is that possible in a pivot table?

 

[Smitty]

If you add your column I to the original data set then you can include it in the PT. If you want to hide PT columns and still need the column itself to be in the PT, then it's as easy as just hiding the column.