Sub total vs Pivot Table
Jeevan wrote:
If the following is my data, Can I use a Pivot table to get a subtotal for
units and a single cost? If I use Sub total, costs also get subtotal which is
incorrect?
Thanks
Style Total Units Cost per unit
220995 -3 13.1
220995 -1 13.1
220995 -1 13.1
220995 -1 13.1
220995 3 13.1
220995 -1 13.1
240995 -7 13.1
240995 -1 13.1
240995 -1 13.1
240995 -1 13.1
240995 4 13.1
250995 -168 7.05
290995 -1 25.2
290995 -1 25.2
A problem is you are mixing unit data (total units) with aggregate data
(cost per unit). Either Subtotal or PT will aggregate both, which is
incorrect for the "cost per" data.
I suggest you either import the actual cost, or manufacture it to the
side. The latter formula might look like this:
Cost = Total Units * Cost per unit
Then you can sum/subtotal Total Units and Cost (not cost per unit!), and
get a correct (average) cost per unit by dividing the subtotals.
|