Aggregate (MDX)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Returns a number that is calculated by aggregating over the cells returned by the set expression. If a numeric expression is not provided, this function aggregates each measure within the current query context by using the default aggregation operator that is specified for each measure. If a numeric expression is provided, this function first evaluates, and then sums, the numeric expression for each cell in the specified set.

Syntax


Aggregate(Set_Expression [ ,Numeric_Expression ])  

Arguments

Set_Expression
A valid Multidimensional Expressions (MDX) expression that returns a set.

Numeric_Expression
A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number.

Remarks

If a set of empty tuples or an empty set is specified, this function returns an empty value.

The following table describes how the Aggregate function behaves with different aggregation functions.

Aggregation Operator Result
Sum Returns the sum of values over the set.
Count Returns the count of values over the set.
Max Returns the maximum value over the set.
Min Returns the minimum value over the set.
Semi-additive aggregation functions Returns the calculation of semi-additive behavior over the set after projecting the shape to the time axis.
Distinct Count Aggregates across the fact data contributing to the subcube when the slicer axis includes a set.

Returns the distinct count for each member of the set. The result depends on the security on the cells being aggregated, and not on the security on the cells that are required for the computation. Cell security on the set generates an error; cell security below the granularity of the specified set is ignored. Calculations on the set generate an error. Calculations below granularity of the set are ignored. Distinct count over a set that includes a member and one or more of its children returns the distinct count across facts contributing to the child member.
Attributes that cannot be aggregated Returns the sum of the values.
Mixed aggregation functions Not supported, and raises an error.
Unary operators Not respected; values are aggregated by summing.
Calculated measures Solve order set to ensure calculated measure applies.
Calculated members Normal rules apply, that is, the last solve order takes precedence.
Assignments Assignments aggregate according to the measure aggregation function. If the measure aggregation function is distinct count, the assignment is summed.

Examples

The following example returns the sum of the Measures.[Order Quantity] member, aggregated over the first eight months of calendar year 2003 that are contained in the Date dimension, from the Adventure Works cube.

WITH MEMBER [Date].[Calendar].[First8Months2003] AS  
    Aggregate(  
        PeriodsToDate(  
            [Date].[Calendar].[Calendar Year],   
            [Date].[Calendar].[Month].[August 2003]  
        )  
    )  
SELECT   
    [Date].[Calendar].[First8Months2003] ON COLUMNS,  
    [Product].[Category].Children ON ROWS  
FROM  
    [Adventure Works]  
WHERE  
    [Measures].[Order Quantity]  

The following example aggregates over the first two months of the second semester of calendar year 2003.

WITH MEMBER [Date].[Calendar].[First2MonthsSecondSemester2003] AS  
    Aggregate(  
        PeriodsToDate(  
            [Date].[Calendar].[Calendar Semester],   
            [Date].[Calendar].[Month].[August 2003]  
        )  
    )  
SELECT   
    [Date].[Calendar].[First2MonthsSecondSemester2003] ON COLUMNS,  
    [Product].[Category].Children ON ROWS  
FROM  
    [Adventure Works]  
WHERE  
    [Measures].[Order Quantity]  

The following example returns the count of the resellers whose sales have declined over the previous time period, based on user-selected State-Province member values evaluated using the Aggregate function. The Hierarchize and DrillDownLevel functions are used to return values for declining sales for product categories in the Product dimension.

WITH MEMBER Measures.[Declining Reseller Sales] AS   
   Count(  
      Filter(  
         Existing(Reseller.Reseller.Reseller),   
            [Measures].[Reseller Sales Amount] < ([Measures].[Reseller Sales Amount],  
            [Date].Calendar.PrevMember)  
            )  
         )  
MEMBER [Geography].[State-Province].x AS   
   Aggregate (   
      {[Geography].[State-Province].&[WA]&[US],   
      [Geography].[State-Province].&[OR]&[US] }   
         )  
SELECT NON EMPTY Hierarchize (  
   AddCalculatedMembers (  
      {DrillDownLevel({[Product].[All Products]})}  
         )  
   )  
        DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS   
FROM [Adventure Works]  
WHERE ([Geography].[State-Province].x,   
    [Date].[Calendar].[Calendar Quarter].&[2003]&[4],  
    [Measures].[Declining Reseller Sales])  

See Also

PeriodsToDate (MDX)
Children (MDX)
Hierarchize (MDX)
Count (Set) (MDX)
Filter (MDX)
AddCalculatedMembers (MDX)
DrilldownLevel (MDX)
Properties (MDX)
PrevMember (MDX)
MDX Function Reference (MDX)