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 measure:

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.