Microsoft Office Tutorials and References
In Depth Information
Moreover, you can easily perform more complex computations—for example, at the country
level for some years, as in Figure 10-27.
FIguRE 10-27 Comparison of freights from different couriers.
This solution is very simple to implement, and if you need to analyze further, you can add
all the columns that interest you in the Orders table to get more complex and interesting
Same Solution with CALCuLATE
Before we end this section, it is worth noting that the previous formula to compute the
freight for a single courier, might be expressed with CALCULATE, as was the case in
the banding example.
BlueYonderFreight = CALCULATE( VALUES( Couriers[Freight] ),
Couriers[Country] = Orders[Country]
&& Couriers[MinWeight] <= Orders[Weight]
&& Orders[Weight] < Couriers[MaxWeight]
&& Couriers[Courier] = "BlueYonder" ) )
This formula is slightly harder to understand because it makes use of the CALCULATE
function. It basically retrieves the values of the Freight column from the Couriers table
after having applied a filter context that filters only the relevant rows. Because you use
it to define a column, this formula raises an error if the number of values returned is
more than one, providing a safer environment whenever errors are detected. The use of
CALCULATE might be slightly better also from a performance point of view. Nevertheless,
the formula is less intuitive than the previous one. It is up to you to choose the best for