Sum

Returns the sum of a numeric expression evaluated over a set.

With a numeric expression: Calculates the numeric expression for each member in the set and returns the total sum.

Without a numeric expression: Returns the sum of the members in the set within the current report context.

Syntax

Sum(Set_Expression, Numeric_Expression)

Arguments

Set_Expression	MDX expression that returns a set for which the sum will be found.
Numeric_Expression	MDX expression that returns a number that will be summed up across the set.

Examples

Issues resolved up to the current Time member

The following example calculates the cumulative amount of Issues resolved over time.

Sum(
  -- set of previous time periods until selected time period
  {
    PreviousPeriods([Time].CurrentHierarchyMember),
    [Time].CurrentHierarchyMember
  },
  -- numeric expression
  [Measures].[Issues resolved]
)

The number of bugs currently in one of several statuses

In this example, we'll pass a set of Status dimension members and a tuple with Issues created to determine how many of the created bugs are currently in one of these statuses.

Sum(
  {
    [Status].[To Do],
    [Status].[In Progress],
    [Status].[Review]
  },
  (
    [Measures].[Issues created],
    [Issue type].[Story]
  )
)

The number of issues resolved before their due date

In the following formula, the Filter function is used to filter through the set of all issues and return a smaller set of issues that align with the filter conditions (issues are resolved, and resolution date has not crossed due date). Next, the Sum function then takes this subset of issues and sums together the value returned by the Issues resolved measure for each issue.

Sum(
  Filter(
    --iterate through a set of all issues one by one
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    -- check that issue has a resolution date
    NOT IsEmpty([Issue].CurrentMember.Get('Resolved at'))
    AND
    -- and that it is resolved before its due date
    DateCompare(
      [Issue].CurrentMember.Get('Due date'),
      [Issue].CurrentMember.Get('Resolved at')
    ) >= 0
  ),
  [Measures].[Issues resolved]
)