Aggregate

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.

Syntax

 Aggregate(Set_Expression [ ,Numeric_Expression ])

Arguments

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

Examples

Design a set by listing individual members within curly brackets. For example, define a new calculated member 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.


The following example returns a set of standard (no sub-task) Issue types.

Aggregate(
Filter(
  [Issue type].[Issue type].Members,
  NOT [Issue type].CurrentMember.GetBoolean('Subtask')
  ))

See report example Project progress % in our demo account.  The calculated member Standard issue types in dimension Issue type uses the formula above. You can select this calculated member to filter and see the project progress for standard issues only.


The following example returns sprints from any board and orders them by start date:

Aggregate(
-- Order function starts
  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'), 
  -- symbol
   BASC)
-- Order function ends
)


See also