Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Pivot table problem...
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. -- Thank for your help in advance BeSmart |
#2
|
|||
|
|||
Pivot table problem...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I am having trouble keeping numbers formatted in a Pivot Table | Excel Discussion (Misc queries) | |||
Pivot Tables, Help? | Excel Discussion (Misc queries) | |||
pivot table | Excel Worksheet Functions | |||
pivot table multi line chart | Charts and Charting in Excel | |||
Pivot Table Problem | Excel Worksheet Functions |