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]
As per msdn
The following table lists the aggregation functions in Analysis Services, and describes both the additivity and expected output of the function.
|Aggregation function||Additivity||Returned value|
|Sum||Additive||Calculates the sum of values for all child members. This is the default aggregation function.|
|Count||Additive||Retrieves the count of all child members.|
|Min||Semiadditive||Retrieves the lowest value for all child members.|
|Max||Semiadditive||Retrieves the highest value for all child members.|
|DistinctCount||Nonadditive||Retrieves the count of all unique child members. For more details, see About Distinct Count Measures in the next section.|
|None||Nonadditive||No aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure. If no value can be read from the fact table for a member, the value for that member is set to null.|
|ByAccount||Semiadditive||Calculates the aggregation according to the aggregation function assigned to the account type for a member in an account dimension. If no account type dimension exists in the measure group, treated as the None aggregation function.
For more information about account dimensions, see Create a Finance Account of parent-child type Dimension.
|AverageOfChildren||Semiadditive||Calculates the average of values for all non-empty child members.|
|FirstChild||Semiadditive||Retrieves the value of the first child member.|
|LastChild||Semiadditive||Retrieves the value of the last child member.|
|FirstNonEmpty||Semiadditive||Retrieves the value of the first non-empty child member.|
|LastNonEmpty||Semiadditive||Retrieves the value of the last non-empty child member.|