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

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.
--
B Rager


"Herbert Seidenberg" wrote:

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

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


"B Rager" wrote:

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.
--
B Rager


"Herbert Seidenberg" wrote:

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

Can you upload the file?

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)
--
B Rager


"Herbert Seidenberg" wrote:

Can you upload the file?