Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Erroneous Grand Total of calculated fields in pivot table
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Erroneous Grand Total of calculated fields in pivot table
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Fields | Excel Worksheet Functions | |||
How do I set up filter for page fields in pivot table? | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
Calculated Field in Pivot Table | Excel Worksheet Functions | |||
pivot table question, sum fields? | Excel Worksheet Functions |