Microsoft Office Tutorials and References
In Depth Information
With the relationship active, you can now easily build a PivotTable that computes the total
weight of an order, as you can see in Figure 10-22.
FIguRE 10-22 The total weight in a PivotTable.
The table looks very similar to the one computed before (see Figure 10-17); the big difference
is that now the total weight is stored in a separate table (the data model is changed) and you
can use simple DAX calculations to bring it into the Orders table as a computed column.
Using the existing relationship, you can take, for each row of the Orders table, all the rows of
the OrderDetails table that are in relationship with an order row. If you sum up the TotalWeight
column for all of the related rows, you can compute the total weight at the order level. The
DAX formula for the Weight column shown in Figure 10-24 is the following:
Weight = CALCULATE( SUM( OrderDetails[TotalWeight] ) )
Note As you have seen previously in the topic, the CALCULATE formula uses the existing
relationship between Orders and OrderDetails table to transform the row context of the Orders row
for which Weight is calculated into a filter context for the OrderDetails table. In this way, the SUM
function operates only on related tables. An equivalent way to write the same DAX operation is
the following formula that uses SUMX and RELATEDTABLE functions. That pattern is also
important to know because it is the only one that can be used whenever you have a more complex
expression to calculate for each line of the order:
Weight = SUMX( RELATEDTABLE( OrderDetails ),
The final form of the Order table looks like Figure 10-23.
FIguRE 10-23 The Order weight computed at the OrderNumber level.