Microsoft Office Tutorials and References
In Depth Information
The result of this function is still a table, which we already know contains exactly one row.
Nevertheless, it is a table and you cannot define a column to contain a table. Because you
want to get the Freight value from the filtered table, you can use an aggregation function,
such as the SUMX function, to compute the sum of all the rows in the filtered table. Because
the filtered table contains only one row, the result is exactly the freight value for the only
row in the filtered table.
The complete formula is the following:
BlueYonderFreight = SUMX( FILTER( Couriers,
Couriers[Country] = Orders[Country]
&& Couriers[MinWeight] <= Orders[Weight]
&& Orders[Weight] < Couriers[MaxWeight]
&& Couriers[Courier] = "BlueYonder" ),
You can define a new calculated column in the orders table with this formula and another
one replacing BlueYonder with WorldWide to get the freight cost of both couriers in two
The resulting table is shown in Figure 10-25.
FIguRE 10-25 The complete Orders table, with computed columns for the freights.
With these two columns, now the task of comparing the different freights is very easy. You
can check it on single orders, as in Figure 10-26.
FIguRE 10-26 Comparison of freights from different couriers.