Average of subtotals in a pivot table

[Rm385]

Hi,

I know that this has been asked multiple times before but I just can't figure out the answer from the answers that I found so far.

So here goes. I have data organized like this (there are multiple fees but this is just an example):

Proj Descr	Acct Descr	Amount	Issue
Feature 1	 Art Fee	    100	    1
Feature 1	 Art Fee	    200	    1
Feature 1	 Art Fee	    300	    1
Feature 2	 Art Fee	    400	    1
Feature 2	 Art Fee	    500	    1
Feature 2	 Art Fee	    600	    1
Feature 3	 Art Fee	    700	    1
Feature 3	 Art Fee	    800	    1
Feature 3	 Art Fee	    900	    1
Feature 3	 Art Fee	    1000	   1
Feature 4	 Art Fee	    1100	   2
Feature 4	 Art Fee	    1200	   2
Feature 4	 Art Fee	    1300	   2
Feature 4	 Art Fee	    1400	   2
Feature 5	 Art Fee	    1500	   2
Feature 5	 Art Fee	    1600	   2
Feature 5	 Art Fee	    1700	   2
Feature 5	 Art Fee	    1800	   2

To get totals of amount for fee for each issue I have created a pivot table. The total for Feature 1 is 600, for Feature 2 is 1500, for Feature 3 is 3400, etc.

Now I need the average of totals for features for each issue. For example, I should see 1833.33 for Issue 1 and 5800 for Issue 2. If I just add average to the pivot table, it averages all the lines that went into the subtotal, and that is not what I need (I get 550 for Issue 1 and 1450 for Issue 2).

I guess I've been thinking about this too much and just can't see a simple solution anymore. What am I missing? Can someone please help me?

Thank you!

 

[texasalynn]

try this

Book2
	A	B	C	D	E
3	Amount	Issue
4	Proj Descr	1	2	Grand Total
5	Feature 1	600		600
6	Feature 2	1500		1500
7	Feature 3	3400		3400
8	Feature 4		5000	5000
9	Feature 5		6600	6600
10	Grand Total	5500	11600	17100
11		1833.3333	5800
Sheet4

after pivot table is created I added the average formulas

HTH
texasalynn

 

[Rm385]

Thanks for the response!

The problem is that my actual data is more complicated. The way the pivot table is set up it has Issue and Proj Descr in the row area (there are dozens of issues, so I want to show them vertically), and Account Descr (there are at least 2 per issue) are in the column area. So I can't figure out how to where to add an average formula.

 

[texasalynn]

would this work
=AVERAGE(SUMIF(B5:B15,1,C5:C14)/COUNTIF(B5:B15,1))

texasalynn

 

[VBA_Newbie]

Is this something like what you wanted?

If it is, here's what I've done. I created calculated items in Issue. Here's the steps.

1. Set up your pivot table where Issue and Project description are the row fields and Amount and Another Amount are the data fields (both calculated as SUMS).
2. Right Click on issue, go to Formulas, and Calculated Items. Create a title for your calculated item (ie. "Issue 1 Average"). The actual formula for Issue 1 Average is Issue[1] /3, where is [1] does not refer to issue 1, is refers to the first item in the field (it's just a coincidence they are the same number in this case). 3 is the number of features for this particular issue.
3. Repeat these steps for "Issue 2 Average", the formula will be Issue[2]/2 because there are 2 feature items.
4. Finally, double click on Issue 1 Average and Issue 2 average to hide the details.

That's it! I hope it works out for you.

Source Data

Book3
	A	B	C	D	E	F
1	Project Description	Acct	Descr	Amount	Another Amount	Issue
2	Feature 1	Art	Fee	$ 100.00	$ 165.52	1
3	Feature 1	Art	Fee	$ 200.00	$ 246.44	1
4	Feature 1	Art	Fee	$ 300.00	$ 525.68	1
5	Feature 2	Art	Fee	$ 400.00	$ 876.15	1
6	Feature 2	Art	Fee	$ 500.00	$ 881.29	1
7	Feature 2	Art	Fee	$ 600.00	$ 381.49	1
8	Feature 3	Art	Fee	$ 700.00	$ 855.95	1
9	Feature 3	Art	Fee	$ 800.00	$ 507.19	1
10	Feature 3	Art	Fee	$ 900.00	$ 283.61	1
11	Feature 3	Art	Fee	$1,000.00	$ 520.67	1
12	Feature 4	Art	Fee	$1,100.00	$ 719.45	2
13	Feature 4	Art	Fee	$1,200.00	$ 942.73	2
14	Feature 4	Art	Fee	$1,300.00	$ 732.74	2
15	Feature 4	Art	Fee	$1,400.00	$ 482.95	2
16	Feature 5	Art	Fee	$1,500.00	$ 290.88	2
17	Feature 5	Art	Fee	$1,600.00	$ 946.28	2
18	Feature 5	Art	Fee	$1,700.00	$ 603.40	2
19	Feature 5	Art	Fee	$1,800.00	$ 248.75	2
Sheet1

Pivot Table

Book3
	H	I	J	K
4	Issue	Project Description	Amount	Another Amount
5	1		$ 5,500.00	$ 6,853.20
6		Feature 1	$ 600.00	$ 2,445.84
7		Feature 2	$ 1,500.00	$ 1,500.17
8		Feature 3	$ 3,400.00	$ 2,907.19
9
10	2		$11,600.00	$ 4,076.95
11		Feature 4	$ 5,000.00	$ 2,383.75
12		Feature 5	$ 6,600.00	$ 1,693.21
13
14	Issue 1 Average		$ 1,833.33	$ 2,284.40
15
16	Issue 2 Average		$ 5,800.00	$ 2,038.48
Sheet1

 

[Mark W.]

You mean like this PivotTable...

Book1.xls
	A	B	C	D
2	Issue	Proj Descr	Sum of Amount
3	1	Feature 1	600
4		Feature 2	1500
5		Feature 3	3400
6	1 Average		1833.333333
7	2	Feature 4	5000
8		Feature 5	6600
9	2 Average		5800
10	Grand Total		17100
Sheet2

 

[Rm385]

Yes, like this table. How do I make it stop averaging all the lines that went into each line in the Sum of Amount column?

 

[Mark W.]

Yes, like this table. How do I make it stop averaging all the lines that went into each line in the Sum of Amount column?

Obviously, the reason the Average summary function wasn't producing the desired results was that the denominator wasn't based on the rows displayed by the PivotTable, but rather on the rows in the underlying data that you originally posted. The "secret" to resolving this is to base your PivotTable on a summary of the original data so that the denominator will be equivalent to the number of rows representing distinct 'Proj Descr' items for each 'Issue' item. This can be accomplished by querying your original data as though it was External Data (using the Excel ODBC driver and MS Query) with this query...

SELECT List.Issue, List.`Proj Descr`, Sum(List.Amount)
FROM Book1`.List List
GROUP BY List.Issue, List.`Proj Descr`

The results of this query...

• Issue `Proj Descr` Amount
  1.0 Feature 1 600.0
  1.0 Feature 2 1500.0
  1.0 Feature 3 3400.0
  2.0 Feature 4 5000.0
  2.0 Feature 5 6600.0

...now becomes the source data for the PivotTable. Then all that's needed is a Custom Subtotal for 'Issue' using Average as the summary function.

(600+1500+3400)/3 = 1833.33...
(5000+6600)/2 = 5800

 

[Rm385]

Wow, this is a bit more complicated that I was hoping for. Thank you! I made it work with the sample data, now I just need to apply the same logic to my original data.