Microsoft Office Tutorials and References
In Depth Information
Then you can select the whole PivotTable, copy it to the Clipboard, and use the Paste button in
PowerPivot to have a new table filled with the data. This solution is very simple for small tables
(in our example we have 27,662 rows to select), but it might be a problem for medium-size
tables, in which you could face millions of rows and selecting them also might be not feasible
because of memory constraints. Moreover, the biggest issue with this approach is that, if the
source data is updated, you need to repeat the copy and paste operation to refresh data. In
the end, this solution works fine for a simple data set, but probably you want a better one.
There are two solutions to this problem, one involving DAX and the other one involving a small
change in the data model. As usual, we are going to present both. The data model solution is
easy but not very intuitive. Even if you cannot create a relationship in a table with itself, you
can still load the same table twice and set up a relationship between the two instances of the
same table. You need to load two different tables into PowerPivot, one that has the granularity
of the order number and one that has the granularity of the order line. Let us call them Orders
and OrderDetails. You compute the weight of a single line at the detail level and then, through
a relationship between orders and details, you can summarize the weight in the orders table,
which has the correct granularity.
You can load the Orders table with the basic information needed for our analysis, as you see
in Figure 10-18.
FIguRE 10-18 The Orders query, with basic information.
Loading this data results in a summarized table, shown in Figure 10-19, which contains
information at the order level, with no details about single products. So in this table, you are missing
the weight of the order.