Microsoft Office Tutorials and References
In Depth Information
The first step in your analysis is solved—you now have a table that contains the basic
information you need to perform the simulation, and that data will be refreshed correctly whenever the
source data changes. It is now time to perform the courier simulation over this table, something
that is harder than expected.
A More Elegant Solution with CALCuLATE and EARLIER
For the brave reader, we have another solution to the same problem, which involves
your using the CALCULATE function with only one table (the OrderDetails table) to
perform the total weight per order computation. Using CALCULATE and the EARLIER
functions, you can summarize the total order weight with this formula:
= CALCULATE( SUM( OrderDetails[Weight] ),
ALL( OrderDetails ),
OrderDetails[OrderNumber] = EARLIER( OrderDetails[OrderNumber] ) )
This solution is easier to implement but requires of you a better understanding of the
DAX language. Because we believe that the solution presented before is easier to
understand, we provided that one first. Using CALCULATE and EARLIER is much harder
to understand, but it gives you a very elegant and neat solution like the one we just
showed you. More information about how this technique works can be found in
Chapter 6, “Evaluation Context and CALCULATE.”
Adding Courier Information
The next step in the simulation example is to add the information about the courier proposals.
To be able to compute, for each order, the freight cost charged by each different courier, you
should prepare an Excel workbook that contains the proposal details. Because the variables
are the geographical location and the weight of the order, you can end up with a worksheet
like the one shown in Figure 10-24.
FIguRE 10-24 The configuration worksheet for the couriers.