Using calculated items in pivot table with multiple row fields

[Straha]

I am having trouble using a calculated item with a pivot table that has multiple row fields. The table below shows correctly.<br>

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	SumofAmt		Month
4	Customer	Location	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	GrandTotal
5	CustomerA	Location1	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	12000
6		Location2	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	12000
7		Location3	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	12000
8	CustomerATotal		3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	36000
9	CustomerB	Location1	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	12000
10		Location4	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	12000
11		Location5	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	12000
12	CustomerBTotal		3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	36000
13	CustomerC	Location5	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	12000
14		Location6	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	12000
15		Location7	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	1000	12000
16	CustomerCTotal		3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	3000	36000
17	GrandTotal		9000	9000	9000	9000	9000	9000	9000	9000	9000	9000	9000	9000	108000
Sheet2

However, when I add a calculated field for Q1 (using field "Month" =Jan+Feb+Mar) every location is displayed for each customer. Like this:<br>

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	SumofAmt		Month
4	Customer	Location	Jan	Feb	Mar	Q1	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	GrandTotal
5	CustomerA	Location1	1000	1000	1000	3000	1000	1000	1000	1000	1000	1000	1000	1000	1000	15000
6		Location2	1000	1000	1000	3000	1000	1000	1000	1000	1000	1000	1000	1000	1000	15000
7		Location3	1000	1000	1000	3000	1000	1000	1000	1000	1000	1000	1000	1000	1000	15000
8		Location4				0										0
9		Location5				0										0
10		Location6				0										0
11		Location7				0										0
12	CustomerATotal		3000	3000	3000	9000	3000	3000	3000	3000	3000	3000	3000	3000	3000	45000
13	CustomerB	Location1	1000	1000	1000	3000	1000	1000	1000	1000	1000	1000	1000	1000	1000	15000
14		Location2				0										0
15		Location3				0										0
16		Location4	1000	1000	1000	3000	1000	1000	1000	1000	1000	1000	1000	1000	1000	15000
17		Location5	1000	1000	1000	3000	1000	1000	1000	1000	1000	1000	1000	1000	1000	15000
18		Location6				0										0
19		Location7				0										0
20	CustomerBTotal		3000	3000	3000	9000	3000	3000	3000	3000	3000	3000	3000	3000	3000	45000
21	CustomerC	Location1				0										0
22		Location2				0										0
23		Location3				0										0
24		Location4				0										0
25		Location5	1000	1000	1000	3000	1000	1000	1000	1000	1000	1000	1000	1000	1000	15000
26		Location6	1000	1000	1000	3000	1000	1000	1000	1000	1000	1000	1000	1000	1000	15000
27		Location7	1000	1000	1000	3000	1000	1000	1000	1000	1000	1000	1000	1000	1000	15000
28	CustomerCTotal		3000	3000	3000	9000	3000	3000	3000	3000	3000	3000	3000	3000	3000	45000
29	GrandTotal		9000	9000	9000	27000	9000	9000	9000	9000	9000	9000	9000	9000	9000	135000
Sheet2

The ideal solution would be to have Q1 only calculate for the locations specific to that customer using the first table in the example instead of having all locations displayed for each customer as in table 2.<br>
Thanks in advance for your help.

 

[Straha]

(bumping to page 1)

Anybody have any ideas on how to fix this problem?

 

[Fazza]

Hi. Don't add a new field for the quarters: there is in-built functionality in the pivot table to handle this (so long as your input data for months are dates). From your months' data in the pivot table, right click from one of the month values, or from the grey 'month' column field marker, and select 'group'. Then select quarters, and if you also want years, pick that both quarters and years. OK?

 

[Straha]

That works. Thanks for help and advice.

 

[Fazza]

You're welcome. There are plenty of places you can learn a bit more about pivot tables. Excel help and Microsoft have excellent information. And links below. Regards, Fazza

http://www.datapigtechnologies.com/ExcelMain.htm
http://www.contextures.com/tiptech.html