TopPercent (MDX)
Sorts a set in descending order, and returns a set of tuples with the highest values whose cumulative total is equal to or greater than a specified percentage.
Syntax
TopPercent(Set_Expression, Percentage, Numeric_Expression)
Arguments
Set_Expression
A valid Multidimensional Expressions (MDX) expression that returns a set.Percentage
A valid numeric expression that specifies the percentage of tuples to be returned.Numeric_Expression
A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number.
Remarks
The TopPercent function calculates the sum of the specified numeric expression evaluated over the specified set, sorting the set in descending order. The function then returns the elements with the highest values whose cumulative percentage of the total summed value is at least the specified percentage. This function returns the smallest subset of a set whose cumulative total is at least the specified percentage. The returned elements are ordered largest to smallest.
Important
Like the BottomPercent function, the TopPercent function always breaks the hierarchy.
Example
The following example returns, for the Bike category, the smallest set of members of the City level in the Geography hierarchy in the Geography dimension whose cumulative total using the Reseller Sales Amount measure is at least the 10% of the overall total (beginning with the members of this set with the largest number of sales).
SELECT [Measures].[Reseller Sales Amount] ON 0,
TopPercent
({[Geography].[Geography].[City].Members}
, 10
, [Measures].[Reseller Sales Amount]
) ON 1
FROM [Adventure Works]
WHERE([Product].[Product Categories].Bikes)