Pivot Table Question

[baggarwal]

Hi All:

I have a question about pivot tables. I set up a pivot table to display the top 3 concerns for the monthy by functiona area. The only problem I ran into is the grand totals for the functional area displays the sum of the top 3 concerns rather than the total of all the concerns.

How can this be fixed?

BA

 

[PaddyD]

See the help file for :

How totals are calculated in PivotTable reports

...by default, excel "calculates totals and subtotals for row fields and column fields, including only the visible items."

You may be able to use the info in the:

Add or remove subtotals in a PivotTable report

...help topic to address your problem, or you may need to manipulte you data on the source sheet to get the desired outcome (e.g. by adding a derived column to you pivot).

paddy

 

[baggarwal]

Hi Paddy:

I tried going into table options and checkmarked Subtotal hidden page items but it didn't calculate the hidden items when I did that. So I am not sure how the data can be manipulated so that it subtotals on the totals and not just the top 3.

When I go into calculated field what do I put in for formula to get everything?

 

[Mark W.]

Hi All:

I have a question about pivot tables. I set up a pivot table to display the top 3 concerns for the monthy by functiona area. The only problem I ran into is the grand totals for the functional area displays the sum of the top 3 concerns rather than the total of all the concerns.

How can this be fixed?

BA

Remove the AutoShow option to show a Grand Total for all values in your data list. Perhaps you should add a Rank field to your list and include it in your PivotTable and AutoSort on the Rank field.

 

[baggarwal]

Hi Mark:

I can't do that. I want to show only the top 3 concerns and have grand totals for all of them all in the same table.

 

[Mark W.]

...Perhaps you should add a Rank field to your list and include it in your PivotTable and AutoSort on the Rank field.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Field1	Field2	Field3	Field4			SumofField2
2	A	397	4	0ther			Field4	Field1	Total
3	B	285	9	0ther			1	F	430
4	C	424	2	2			2	C	424
5	D	251	10	0ther			3	J	422
6	E	306	7	0ther			0ther		2264
7	F	430	1	1			GrandTotal		3540
8	G	292	8	0ther
9	H	362	6	0ther
10	I	371	5	0ther
11	J	422	3	3
12
13
14
15
Sheet2