Aggregate

The Aggregate function can be used in two ways:

It returns a number calculated by aggregating 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 calculated 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 the 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.

Last 6 months

The following example aggregates the month level members representing the last 6 months including the current month. Create this calculated member in the Time dimension:

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

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
  )
)