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 ),

OrderDetails[TotalWeight] )

The final form of the Order table looks like Figure 10-23.

FIguRE 10-23
The Order weight computed at the OrderNumber level.