Microsoft Office Tutorials and References
In Depth Information
FIguRE 10-16 Using PowerPivot, you are now able to aggregate the weight per order.
It might seem that you have solved the problem, but you are not at the end of the trip. Even if
you can easily produce this report, aggregating data by order number, this is not what you
really want to do. You want to add the total weight of an order as a new calculated column
in the PowerPivot table so that you can compute the freight cost for different couriers.
Up to now, you succeeded only in computing the value in a PivotTable, but your needs
are quite different.
To compute the total weight, you should compute, for each order, the sum of all the weights
of the rows that have the same order number. Nevertheless, it is not easy to perform such a
computation over a single table because you need a table that is related to itself, which is
not handled by PowerPivot.
The quick-and-dirty solution is straightforward, even if it is not very elegant. You can create a
PivotTable that contains the order number and the sum of weight, as shown in Figure 10-17.
FIguRE 10-17 Orders and total weight, in a PivotTable.