Microsoft Office Tutorials and References
In Depth Information
Other Aggregation Functions and the CALCuLATE Syntax
In all the examples, we have used the SUM aggregation function. You might need to use
other aggregation functions, such as AVERAGE, or more complex formulas. Whenever
you saw SUM( SalesOrderDetail[LineTotal] ) in the previous example, consider that you
can always replace such expressions with another DAX formula, also by simply replacing
the aggregation function.
In case your calculation becomes more complex, you might prefer to specify that
calculation, which can be shared across several other measures, into a separated measure,
containing just this operation. In this way, you can avoid the duplication of the
aggregation function in all the formulas for measures that make special calculations for dates.
For example, you might want to calculate the weighted average price with the
following formula, assigned to measure AveragePrice:
AveragePrice = SUM( SalesOrderDetail[LineTotal] ) / SUM( SalesOrderDetail[OrderQty] )
You can use a direct reference to that measure, without using an aggregation
function, whenever you use formulas such as CALCULATE or special Time Intelligence
functions that behave like CALCULATE, as in the year-to-date calculation defined in
YtdAveragePrice = TOTALYTD( SalesOrderDetail[AveragePrice] , OrderDate[Date] )
The calculation for the prior year can be written by using the CALCULATE function:
PyAveragePrice = CALCULATE( SalesOrderDetail[AveragePrice],
SAMEPERIODLASTYEAR( OrderDate[Date] ) )
In Figure 7-43, you can see the results of these formulas in a PivotTable.
FIguRE 7-43 The year-to-date and prior year calculations for Average Price.