Average aggregation in measures – SSAS Cube

As nicely described in the blog

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:

  1. Create a measure using the Sum aggregation type (which is also the default). In our example, let’s call it Amount.
  2. Create a Count of Non-Empty Values (or Count of Rows) measure. In example – [Measure Count].
  3. 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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s