In SSAS we do not have a measure Average aggregation type. We do have AverageOfChildren (or Average over time), however it is semi-additive and works only along a Time dimension. Fortunately, we have Sum and Count, and since Average = Sum / Count, we can build our own Average aggregation when we need one.
To do that:
- Create a measure using the Sum aggregation type (which is also the default). In our example, let’s call it Amount.
- Create a Count of Non-Empty Values (or Count of Rows) measure. In example – [Measure Count].
- Create the actual calculation – [Measures].[Amount]/[Measures].[Measure Count]
We can either create a calculated measure, which performs the MDX calculation above:
CREATE MEMBER CURRENTCUBE.[Measures].[Average Amount]
AS
[Measures].[Amount]/[Measures].[Measure Count]
,NON_EMPTY_BEHAVIOR = {[Measures].[Measure Count]}
,VISIBLE=1;
, or if we really do not need the Sum base measure, we can set it to be replaced by the calculation with a SCOPE statement:
SCOPE([Measures].[Amount]);
This = [Measures].[Amount]/[Measures].[Measure Count];
NON_EMPTY_BEHAVIOR(This) = [Measures].[Measure Count];
END SCOPE;
Voila! We have created a measure simulating an Average aggregation type. Then, we can hide the Count helper measure and from user point of view there is no evidence of our effort.
Since the count will never be 0, we do not have to say “If not 0, divide, else – do not” and the NON_EMPTY_BEHAVIOR query hint may in fact improve performance, since the calculation will not be performed when the Count measure is NULL (instead of resulting in NULL/NULL=NULL).
Mosha has previously blogged about NON_EMPTY_BEHAVIOR and division by zero and I strongly recommend reading his post.
Another important consideration, which depends on the business scenario is the type of the Count aggregate. It could be Count of rows (Row Bound) or Count of non-empty values (Column Bound). The difference is whether we want to include or exclude the empty values from our aggregate. Either way, the described technique will work equally well.
I realise that this is a well-known approach, but since it is hard to find the solution online I thought it may be interesting for some less-experienced developers.
Boyan Penev on Microsoft BI 