TopPercent (MDX)

Article
02/28/2023

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.

Important

Percentage needs to be a positive value; negative values generate an error.

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.

Warning

If Numeric_Expression returns any negative value then TopPercent returns only one (1) row.

See the second example for a detailed presentation of this behavior.

Important

Like the BottomPercent function, the TopPercent function always breaks the hierarchy.

Examples

A. Return TopPercent

The following example returns the best cities that help make the top 10% of the resellers' sales, for the Bike category. The result is sorted in descending order, beginning with the city that has the highest value 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])

The above expression produces the following results:

City	Reseller Sales Amount
Toronto	$3,508,904.84
London	$1,521,530.09
Seattle	$1,209,418.16
Paris	$1,170,425.18

The original set of data can be obtained with the following query and returns 588 rows:

SELECT [Measures].[Reseller Sales Amount] ON 0,  
Order  
   ({[Geography].[Geography].[City].Members}  
   , [Measures].[Reseller Sales Amount]  
   , BDESC  
   ) ON 1  
FROM [Adventure Works]  
WHERE([Product].[Product Categories].[Bikes])

B. Understand the effect of negative values

The following walkthrough will help understand the effect of negative values in the Numeric_Expression. First let's build some context where we can present the behavior.

The following query returns a table of Resellers 'Sales Amount', 'Total Product Cost' and 'Gross Profit', sorted in descending order of profit. Please note there are only negative values for profit; so, the smallest loss appears at the top.

SELECT { [Measures].[Reseller Sales Amount], [Measures].[Reseller Total Product Cost], [Measures].[Reseller Gross Profit] } ON columns  
     ,  ORDER( [Product].[Product Categories].[Bikes].[Touring Bikes].children, [Measures].[Reseller Gross Profit], BDESC )   ON rows  
FROM [Adventure Works]

The above query returns the following results; rows from the middle section were removed for readability.

Touring Bikes	Reseller Sales Amount	Reseller Total Product Cost	Reseller Gross Profit
Touring-2000 Blue, 50	$157,444.56	$163,112.57	($5,668.01)
Touring-2000 Blue, 46	$321,027.03	$333,021.50	($11,994.47)
Touring-3000 Blue, 62	$87,773.61	$100,133.52	($12,359.91)
...	...	...	...
Touring-1000 Yellow, 46	$1,016,312.83	$1,234,454.27	($218,141.44)
Touring-1000 Yellow, 60	$1,184,363.30	$1,443,407.51	($259,044.21)

Now, if you were asked to present the top 100% bikes by profit you would write a query like:

SELECT { [Measures].[Reseller Sales Amount], [Measures].[Reseller Total Product Cost], [Measures].[Reseller Gross Profit] } ON columns  
     ,  TOPPERCENT( [Product].[Product Categories].[Bikes].[Touring Bikes].children, 100,[Measures].[Reseller Gross Profit] )   ON rows  
FROM [Adventure Works]

Please note that the query asks for one hundred percent (100%); that means all rows should be returned. However, because there are negative values in the Numeric_Expression , only one row is returned.

Touring Bikes	Reseller Sales Amount	Reseller Total Product Cost	Reseller Gross Profit
Touring-2000 Blue, 50	$157,444.56	$163,112.57	($5,668.01)

Share via