Microsoft Office Tutorials and References
In Depth Information
Note If you try to replace the OrdersInPlace measure reference with its definition in the
OrdersInPlaceDailyAverage expression, you have to remove the MAX calls and obtain the same
formula that you use to define the OrdersInPlaceDay calculated column, further along in this
section. The reason is that in the AVERAGEX call, there is a row context that makes the MAX call
unnecessary and also wrong because it would be resolved outside of the filter context defined by
AVERAGEX; that is, it returns the same date instead of using the date iterated by AVERAGEX.
Finally, you can do the same calculation as a calculated column, so that it will not be calculated
dynamically; instead, it will be stored as a column in the Calendar table. As a side effect, the
average of this measure for a period of dates also will be faster to calculate. However, because
performance is usually a minor issue in PowerPivot, it is better to give priority to the
requirements of a dynamic calculation, if you need it. You can define the OrdersInPlaceDay calculated
column in this way:
OrdersInPlaceDay = CALCULATE( COUNTROWS( SalesOrderHeader ),
FILTER( VALUES( SalesOrderHeader[OrderDate] ),
Calendar[Date] >= SalesOrderHeader[OrderDate] ),
FILTER( VALUES( SalesOrderHeader[ShipDate] ),
Calendar[Date] <= SalesOrderHeader[ShipDate] ) ) )
The only difference in the OrdersInPlace measure definition is that there is no MAX
aggregation function to get the Calendar[Date] value. In this case, a row context is active (the current
row in Calendar table for which the column is calculated) so there is always only one value
for that expression. (In the previous definition, if a PivotTable had a selection of a set of dates,
you used MAX to get only the last date of that selection.) You can see the OrdersInPlaceDay
calculated column in the Calendar table in Figure 9-26.
FIguRE 9-26 The OrdersInPlaceDay calculated column in the Calendar table.
In this chapter, you saw how to use DAX in both measures and calculated columns to calculate
ratios, percentages, and standard deviation. You also saw how to make ranking and ABC (Pareto)
analyses. Finally, you learned a technique for calculating an event in progress over time.