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.