Aggregate

The Aggregate function can be used in two ways:

Returns a number that is calculated by aggregating over the members returned by the set expression. If a numeric expression is provided, this function first evaluates and then sums each cell's numeric expression in the specified set. If a numeric expression is not provided, this function aggregates each member within the current query context by using the default aggregation operator specified for each measure.
Group dimension members from the same hierarchy level.

We recommend using the Aggregate function for calculated members to group dimension members.

But for calcauted measures, use function Sum() to sum up numeric values, especially when addressing historical measures or distinct count measures.

Syntax

Aggregate(Set_Expression [, Numeric_Expression])

Arguments

Set_Expression	MDX expression that returns set.
Numeric_Expression	MDX expression that returns a number.

Examples

Aggregate the Highest and High priorities in the Priority dimension

Design a set by listing individual members within curly brackets. For example, define a new calculated member in the Priority dimension for two highest priorities by listing priority members Highest and High:

Aggregate({
  [Priority].[Highest],
  [Priority].[High]
})

See report example Issues created high vs low priority this and previous Year in our Demo account. The calculated member Highest & High in dimension Priority uses the formula above. The report uses this calculated member to compare issues with the highest and high priorities to other priorities.

Resolved issues in the last 6 months

The following example calculates the total number of resolved issues for the last 12 months. Create this measure in the Measure dimension:

Aggregate(
  [Time].[Month].DateMembersBetween('6 months ago', 'today'),
  [Measures].[Issues resolved]
)

This formula uses the DateMembersBetween function to create a set of monthly members from 12 months ago until today. The Aggregate function then sums up the number of resolved issues for each month in this set, giving the total number of issues resolved in the last 12 months.

Aggregate of closed sprints ordered by the sprint start date

The following example returns sprints from any board and orders them by the sprint start date. Create this calculated member in the Sprint dimension:

Aggregate(
  Order(
    -- set
    Filter([Sprint].[Sprint].Members,
      [Measures].[Sprint Closed?] = 'Yes' AND
      NOT isEmpty([Sprint].CurrentMember.Get('Complete date'))
    ),
    -- value for ordering
    [Sprint].CurrentMember.Get('Start date'), 
    -- direction of ordering
    BASC
  )
)