calculating differences in a pivot table

[thooper]

I use a pivot table to show sales by customer. i am looking at sales
for year 2006 and 2007. i placed the customer name field in the row
label, and the sales amount field in the values area of the pivot
table. i placed the year field in the column label to look at the 2
years seperately. is there a way to use the pivot table to calculate
the difference between the 2 years for each customer rather than
calculating the grand total for the 2 years for each customer.
thanks.
T

 

[Roger Govier]

Hi

In your PT, right click and choose Table Options. De-select Grand Totals by
Row.
Right click again and choose Pivot Table Wizard>Layout>drag Sales amount to
the data area a second time.
Double click on the Sum of Sales2 icon and from the dropdown Show Data
as>choose Difference From>Base Field Year>Base Item 2006>OK.OK.Finish.

On the PT, drag the Data button and drop on Total and you will see the
values side by side.

 

[hoopertravis]

Hi

In your PT, right click and choose Table Options. De-select Grand Totals by
Row.
Right click again and choose Pivot Table Wizard>Layout>drag Sales amount to
the data area a second time.
Double click on the Sum of Sales2 icon and from the dropdown Show Data
as>choose Difference From>Base Field Year>Base Item 2006>OK.OK.Finish.

On the PT, drag the Data button and drop on Total and you will see the
values side by side.

--
Regards
Roger Govier






- Show quoted text -

Thanks. I removed the grand totals for each row and i added a 2nd
field for sum of sales amt. then i changed the data for the 2nd sales
amt field to be displayed as a difference from base yr 2006 like you
said above. I get #N/A as the resulting value. Also, i am not sure
it makes a difference, but i am using Excel '07. Thanks.
T

 

[Roger Govier]

Hi

No, it works in XL2007 as well.
If you want, send me a copy of your file and I will set it up for you.
To send direct, email to
roger at technology4u dot co dot uk

Do the obvious with at and dot in the email address.