Microsoft Office Tutorials and References
In Depth Information
You might now wonder why we have spent a number of pages explaining different solutions
when there is an elegant and compact one that works well. The reason is simply that this last
solution is not so evident. It becomes obvious as soon as you read it, but the process of writing
this formula does not come at a glance.
As frequently happens with PowerPivot, you can find many solutions to a single problem.
We strongly prefer this latter one, but we still believe that the previous ones are interesting
to know and understand because they can be applied to several different scenarios, some of
which we are going to show in the next section of this chapter.
Performing Courier Simulation
The previous examples were pretty simple because they concentrated on a single topic.
We are now going to present a much more complex situation, in which you adapt the data
model to your needs and learn some complicated DAX formulas to get a working report.
Let us suppose that, for the next fiscal year, you would like to evaluate a new courier for
the shipping of orders your business sells through the Internet. You have two proposals
from different couriers (named BlueYonder and WorldWide), and you want to evaluate
which one is the best.
Each courier has different shipping charges, all of which are based on both the total weight
of the shipment and its destination. Because you do not have provisional information about
sales in the next year, you want to perform the analysis based on the past. You would like
to carry out a simulation on all the past shipments, applying the different charges of the
couriers to each shipment, and you want to obtain a report that shows the freight charge
of both couriers. The courier with the lower cost in the simulation of the past is your future
We rely on the AdventureWorks data warehouse to perform such simulation, which you can
find on the companion workbook CH10-03-CourierSimulation.xlsm. Unfortunately, the data
warehouse does not contain all the information you need to perform the analysis. So you
are forced to perform some adjustments to the data.
Warning Beware that several of the reports we are going to show are very slow to produce
on a standard computer. We suggest that you follow the reports in the topic instead of trying
to reproduce them. If you want to try them, please be patient and let the computer run for
some minutes before the queries are completed. Moreover, if you want to refresh data coming
from AdventureWorks, you need to install the AdventureWorks OLAP database on an instance
of Analysis Services; otherwise, you get an error every time you try to interact with PivotTables
connected to Analysis Services. Ask your IT department whether they can provide such a service.