Excel 2007 : calculating a cumulative variance to goal in a pivot table

Hello everyone,

I'm new to the group so i hope no one has asked this question...

I've got a problem trying to figure out how to get a pivot table to calculate a cumulative variance to goal.

The pivot table is adding everything for each month correctly except when i try to do the calculation. variance to goal is essentially (x - y)/x. The pivot table is smart enough to know to only grab the date for the month in the data table instead of looking at the cumulative totals already added together within the pivot table...

is this clear as mud??

Example:

Pivot table data:

X Y
June 50 100
July 200 500
Aug 300 800

Data Table data:

X Y
June 50 100
July 150 400
Aug 100 300

The pivot is adding the previous months data to the current months data as it should. however, when i calculate (x-y)/x it's looking at the data table and not the already calculated pivot table.

(50-100)/50 = -1, (200-500)/200 = -1.5 BUT the pivot table calculated field doesn't see the 200 and 500 like i want it to, it's seeing the 150 and 400 and those aren't the numbers i want it to read.

can anyone help??

thanks!
t

.....just go ask Lyndsy and get it done.....