Pivot Table Subtotals do not add properly

[B Rager]

I have a pivot table displayed in monthly columns.
I have rows grouped by a vehicle make, then by a vehicle model.
First value: Under each vehicle make, vehicle model, and month I have a row
value displayed as a whole number. This data is supplied from separate
worksheet.
Second value: I then inserted a calculated field to display a percentage
(this percentage is displayed in each month directly below each whole number)
Fields for the calculation is supplied from the same separate worksheet as
first value.
Third value: I then inserted a calculated field to display the result of
the percentage*whole number. (First value * second value)
Individually by vehicle make and vehicle model the calculations work great,
however, when the subtotals are listed, the % and the totals DO NOT add up at
the bottom. The right hand totals add up fine.
Note: The % displayed by model are consistent across the monthly columns,
but are different for each model.

Sample of one model:
0804 0805 0806 0807 0808 0809 Total
66 54 69 59 83 47 378
65% 65% 65% 65% 65% 65% 65%
43 35 45 38 54 31 246

First line is the monthly dates:
The top (1) whole value will add correctly at the subtotal and total value
under each model, but the % and the calculated (%*top value=) bottom value
does not add up correctly.

Any help would be greatly appreciated.

 

[Herbert Seidenberg]

Instead of a calculated field,
add a column to your source data:
http://www.freefilehosting.net/download/3e4b0

 

[B Rager]

I tried that as well. My totals work for the first and third values, but the
% values are not accurate in the totals/subtotals.

Thank you for the example, but the months have to be displayed from left to
right in order for me to give totals for each month for all models. The
table is very large (I only provided a small piece of the information)
sorted by:
Auction/model/make

The data to the right is fine when totaled (the % remain the same across the
months, so the math works). It's when it starts subtotaling/totaling at the
bottom of each month (when the % are then different) it goes a little crazy.
Its trying to "Average" all the different % at the auction level.

I realize this is probably confusing. Its a little difficult to explain.

Maybe this will help:
Here's the data columns:

Auction
Make
Model
VIN Count
Date (yymm)
3 Month Return Rate %
Forecasted Returns

I need grouped by Auction, Make, Model to display by month the VIN count, 3
Month Return Rate %, and Forecasted Returns.

VIN Count * 3 Month Return Rate % = Forecasted Returns

At the end of each Auction, the VIN count should add up to a total. The
Forecasted Returns should add up to a total which is a % of the VIN Count.

This part actually works, but when it gets to the overall total of all the
auctions, the % appears to be "average of average of average" BUT the total
Forecasted Returns value is the total. If you do math for the overall total,
it does not work. (VIN Count * 3 Month Return Rate % = Forecasted Returns) so
when I show the totals on a separate report, not good that it doesn't match.

 

[Herbert Seidenberg]

Can you upload the file?

 

[B Rager]

I think I have it. I have calculated outside of the pivot:
VIN Count * 3 Month Return Rate % = Forecasted Returns (by month)

In the pivot I plug in the values for "VIN Count" and "Forecasted Returns",
however at that point I create a "calculated field" to back into the "3
Month Return Rate %" (instead of using the value from the data table. Eeven
though its the same value, the pivot treats it differently).

The totals now use the % by weighing the averages (??) and now my totals work.

 

[B Rager]

Using freefilehosting? Let me fudge with the numbers, titles, etc. due to
the sensitivity of the data and see if I can upload it. Looks easy. Give me
about 2 hours. (I have a meeting)