Erroneous Grand Total of calculated fields in pivot table

I'm accessing an external DB to retrieve Quantities and Unit Weights, e.g.
Customer A bought 3 items of product X, with unit weight 10, and Customer B
bought 5 items of product Y, unit weight 12. In a pivot table, I'd like to
get the total weight, so I created a calculated field, Total Weight, as
Quantity*Unit Weight.

That works fine for individual customers A and B: I get respectively 30
(3*10), and 60 (5*12). But the Grand Total of the table is wrong: instead of
90 (30 + 60), I get 176! What happens is that, to calculate the Grand Total,
the table adds all the Quantities (3+5=8), adds all Unit Weights (10+12=22),
then multiplies them (8*22=176)!

How can I use calculated fields, still get correct Grand Totals?

I'm using Excel 2003.

You should ask the database administrator to create a query that
calculates the total weight, then use that field in your pivot table.

In addition to problems with the grand total, you could get incorrect
results if Customer A has multiple orders for product X. In that case,
the Unit weight would be included per order, and multiplied by the
number of items. So, for 3 orders of 3 items each, the total weight
would calculate as 9*30, instead of 9*10.

JP wrote:
> I'm accessing an external DB to retrieve Quantities and Unit Weights, e.g.
> Customer A bought 3 items of product X, with unit weight 10, and Customer B
> bought 5 items of product Y, unit weight 12. In a pivot table, I'd like to
> get the total weight, so I created a calculated field, Total Weight, as
> Quantity*Unit Weight.
>
> That works fine for individual customers A and B: I get respectively 30
> (3*10), and 60 (5*12). But the Grand Total of the table is wrong: instead of
> 90 (30 + 60), I get 176! What happens is that, to calculate the Grand Total,
> the table adds all the Quantities (3+5=8), adds all Unit Weights (10+12=22),
> then multiplies them (8*22=176)!
>
> How can I use calculated fields, still get correct Grand Totals?
>
> I'm using Excel 2003.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thank you, Debra. I conclude there's no way to do this within a PT itself.
Thanks for your quick reply, it saves me from wasting time looking for
something that doesn't exist...

"Debra Dalgleish" wrote:

> You should ask the database administrator to create a query that
> calculates the total weight, then use that field in your pivot table.
>
> In addition to problems with the grand total, you could get incorrect
> results if Customer A has multiple orders for product X. In that case,
> the Unit weight would be included per order, and multiplied by the
> number of items. So, for 3 orders of 3 items each, the total weight
> would calculate as 9*30, instead of 9*10.
>
> JP wrote:
> > I'm accessing an external DB to retrieve Quantities and Unit Weights, e.g.
> > Customer A bought 3 items of product X, with unit weight 10, and Customer B
> > bought 5 items of product Y, unit weight 12. In a pivot table, I'd like to
> > get the total weight, so I created a calculated field, Total Weight, as
> > Quantity*Unit Weight.
> >
> > That works fine for individual customers A and B: I get respectively 30
> > (3*10), and 60 (5*12). But the Grand Total of the table is wrong: instead of
> > 90 (30 + 60), I get 176! What happens is that, to calculate the Grand Total,
> > the table adds all the Quantities (3+5=8), adds all Unit Weights (10+12=22),
> > then multiplies them (8*22=176)!
> >
> > How can I use calculated fields, still get correct Grand Totals?
> >
> > I'm using Excel 2003.
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

Thank you, Debra. I conclude there's no way to do this within a PT itself.
Thanks for your quick reply, it saves me from wasting time looking for
something that doesn't exist...

"Debra Dalgleish" wrote:

> You should ask the database administrator to create a query that
> calculates the total weight, then use that field in your pivot table.
>
> In addition to problems with the grand total, you could get incorrect
> results if Customer A has multiple orders for product X. In that case,
> the Unit weight would be included per order, and multiplied by the
> number of items. So, for 3 orders of 3 items each, the total weight
> would calculate as 9*30, instead of 9*10.
>
> JP wrote:
> > I'm accessing an external DB to retrieve Quantities and Unit Weights, e.g.
> > Customer A bought 3 items of product X, with unit weight 10, and Customer B
> > bought 5 items of product Y, unit weight 12. In a pivot table, I'd like to
> > get the total weight, so I created a calculated field, Total Weight, as
> > Quantity*Unit Weight.
> >
> > That works fine for individual customers A and B: I get respectively 30
> > (3*10), and 60 (5*12). But the Grand Total of the table is wrong: instead of
> > 90 (30 + 60), I get 176! What happens is that, to calculate the Grand Total,
> > the table adds all the Quantities (3+5=8), adds all Unit Weights (10+12=22),
> > then multiplies them (8*22=176)!
> >
> > How can I use calculated fields, still get correct Grand Totals?
> >
> > I'm using Excel 2003.
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>