Aggregates over Aggregates in DAX

Sometimes, you might run into a scenario that requires an aggregate over an aggregate. Consider the following report:

The AvgOrderAmount has a simple calculation: SUM(Sales)/SUM(Qty). This calculation is applied uniformly. The Total line would divide 300/7 and will return 42.86. However, what is the user wants the result in the DesiredAvgOrderAmt column which produces a simple average over the details (50+40)/2. This could be accomplished by creating a base measure for the detail calculation:

DesiredAvgOrderAmtBase := DIVIDE ( SUM ( [Sales] ), SUM ( [Qty] ) )

Then, the DesiredAvgOrderAmt formula would be:

DesiredAvgOrderAmt :=
IF (
    HASONEFILTER ( Customer[Customer] ),
    [DesiredAvgOrderAmtBase],
    AVERAGEX ( VALUES ( Customer[Customer] ), [DesiredAvgOrderAmtBase] )
)

HASONEVALUE would return TRUE when the calculation is performed at the detail level and FALSE in the “grand totals”. In the latter case, it performs a simple average over the detail aggregates. Think of it as performing a second pass over the details to produce an aggregate over aggregates.