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.