Pivot subtotal issue

[Lisa]

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,

 

[Roger Govier]

Hi Lisa

If you look at the field concerned> Options>Show 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.

 

[Lisa]

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