Pivot table calc field problems

  • Thread starter Thread starter R MDld
  • Start date Start date
R

R MDld

I am using a pivot table to summarize sales data from
lists of items sold, date ranges they were sold in,
quantities for each item and the sales price. I need to
multiply the price and quantity to get the total amount
(TotalPrice) for each sale which is then displayed and
summarized in the data area of a pivot table. I am using
a simple formula in a calculated field for this, however
the subtotals for each item are wrong. The strange thing
is. The subtotals are correct when there is only one
TotalPrice summarized in the subtotal. If there is more
than one item in the column (of the subtotal) or more
than one item in the subtotal row being grand totaled, it
seems to multiply the subtotal by 2. I have checkt the
Sum of TotalPrice for the data area subtotals and it all
seems OK. I have also tried making this calculation on
the underlying datasheet (another sheet in the workbook)
and this subtotals correctly but with the calculated
field it doesn't. Any ideas?

Thanks,
RM
 
You should do the calculation in the underlying datasheet, and use that
field in the pivot table.

If you use a calculated field in the pivot table, it will multiply the
sum of quantity for each item and the sum of price.
 
I see, Then these calculated fields have some unexplained
tricks to understand. Thanks for the info, Debra. By the
way, I think your website is great, Frank Kabel referred
me to it earlier and I got some great ideas from your use
of Validation.
Thanks
RM
 
Back
Top