Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot subtotal issue
Okay, I'm pulling my hair out here. I have a pivot issue and no one I work
with can fix it either, so I'm reaching out to you gurus! I am running an Excel pivot using data in an Access query (100,000+ rows, Excel 2003). The data includes sales, pounds of product, months, and years. I am attempting to pivot it out so all the data runs across the top - so years broken down into months, with each month showing sales, pounds, sales yoy, and pounds yoy. It all seems to be fine until I get to the automatic subtotal column for 2007. There, the "pounds yoy" data appears to be comparing the 2007 pounds number to the 2006 SALES number, not pounds. Because of that, the table is showing -90% growth when in fact it's a +2% growth for this year. I have tried running the pivot in a couple of different ways (breaking down the Access query, re-checking the data, etc), and the problem remains. However, if I re-format the table to show the data points in the Row area instead of Column, the problem resolves itself. I'll do that if I have to, but I'm trying to get this darn thing to work the way it's supposed to!! Help?? Many thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot subtotal issue
Hi Lisa
If you look at the field concerned OptionsShow Data as what is the setting? If you say it works when you have it as a row field, but not as a column, I suspect you are saying % of the wrong thing in that comparison. -- Regards Roger Govier "Lisa" wrote in message ... Okay, I'm pulling my hair out here. I have a pivot issue and no one I work with can fix it either, so I'm reaching out to you gurus! I am running an Excel pivot using data in an Access query (100,000+ rows, Excel 2003). The data includes sales, pounds of product, months, and years. I am attempting to pivot it out so all the data runs across the top - so years broken down into months, with each month showing sales, pounds, sales yoy, and pounds yoy. It all seems to be fine until I get to the automatic subtotal column for 2007. There, the "pounds yoy" data appears to be comparing the 2007 pounds number to the 2006 SALES number, not pounds. Because of that, the table is showing -90% growth when in fact it's a +2% growth for this year. I have tried running the pivot in a couple of different ways (breaking down the Access query, re-checking the data, etc), and the problem remains. However, if I re-format the table to show the data points in the Row area instead of Column, the problem resolves itself. I'll do that if I have to, but I'm trying to get this darn thing to work the way it's supposed to!! Help?? Many thanks, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot subtotal issue
That's the odd thing - the field settings look correct. It shows:
Source Field: Pounds Summarize By: Sum Show Data As: % Difference From Base Field: Year Base Item: 2006 "Roger Govier" wrote: Hi Lisa If you look at the field concerned OptionsShow Data as what is the setting? If you say it works when you have it as a row field, but not as a column, I suspect you are saying % of the wrong thing in that comparison. -- Regards Roger Govier "Lisa" wrote in message ... Okay, I'm pulling my hair out here. I have a pivot issue and no one I work with can fix it either, so I'm reaching out to you gurus! I am running an Excel pivot using data in an Access query (100,000+ rows, Excel 2003). The data includes sales, pounds of product, months, and years. I am attempting to pivot it out so all the data runs across the top - so years broken down into months, with each month showing sales, pounds, sales yoy, and pounds yoy. It all seems to be fine until I get to the automatic subtotal column for 2007. There, the "pounds yoy" data appears to be comparing the 2007 pounds number to the 2006 SALES number, not pounds. Because of that, the table is showing -90% growth when in fact it's a +2% growth for this year. I have tried running the pivot in a couple of different ways (breaking down the Access query, re-checking the data, etc), and the problem remains. However, if I re-format the table to show the data points in the Row area instead of Column, the problem resolves itself. I'll do that if I have to, but I'm trying to get this darn thing to work the way it's supposed to!! Help?? Many thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
% of subtotal in pivot table | Excel Discussion (Misc queries) | |||
Subtotal in a pivot table | Excel Discussion (Misc queries) | |||
% of subtotal in pivot Table | Excel Discussion (Misc queries) | |||
Subtotal vs Pivot table - or best way | Excel Discussion (Misc queries) | |||
Pivot tables % of subtotal | Excel Discussion (Misc queries) |