Because your sales amounts are in separate columns in the source data,
you can't automatically create monthly subtotals in the pivot table.
If there are a limited number of products, you could create a calculated
item (PivotTableFormulasCalculated Item) to sum the products. However,
that may create extra rows, with zero product sales, in the pivot table.
BeSmart wrote:
Hi All
I have a pivot table that is generated from a database (example below) that
will continue to be updated and added too each month for a year:
Product M/N Medium Desc Jul 2005 August September October
QC046 A Medium 1 9817.55 12114.86 11133.10
QC046 A Medium 2 6954.33 8581.64 7886.21
QC046 A Medium 3 11245.5 13876.95 12752.40
QC046 A Medium 4 15023.66 18539.20 17036.83
QC046 A Medium 5 10538.86 13004.95 11951.07
QD002 B Medium 6 5242.59 6469.36 5945.10
QG004 A Medium 1 27614.33 34076.08 31314.65
QG004 A Medium 3 5383.03 6642.66 6104.36
QG004 A Medium 5 37840.54 46695.23 42911.17
QG004 D Medium 7 7207.2 8893.68 8172.96
QG004 B Medium 6 17426.1 21503.81 19761.20
QG004 B Medium 8 3456 4264.70 3919.10
QG004 B Medium 9 55606.82 68618.82 63058.13
QG004 B Medium 10 7917.69 9770.43 8978.66
QG004 B Medium 10 8285 10223.69 9395.19
(Note M/N = Master Network)
I can get a great pivot table from this data and I've formatted the table so
the products become columns within each monthly heading and the Master
Network and Medium become rows. eg
Jul 2005 Aug 2005
M/N Medium QC046 QD002 QG004 QC046 QD002 etc...
A Medium 1 9817.55 27614.33 9817.55
Medium 2
etc...
Total for A
B Medium 6
Total for B
Grand Total
I have selected Grand Total for row and column and got totals at the bottom
and totals for each month at the far right of the report.
My problem is that the row totals that are at the far right side of the
report need to appear at the end of each months group of products rather than
at the far end.
Can anyone help me to either:
- Move the totals from the far right to after the last product in each
month (I've tried and it won't let me move subtotal columns).
- Create a subtotal column at the end of the last product column for July
and total each row within the months column range.
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html