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

Couriers[Freight] )

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

distinct columns.

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.