MDX TopPercent Function

In Multidimensional Expression, TopPercent function will sort the given data in the descending order and then selects the required number of records from the sorted data whose total is greater than or equal to specified Percentage.

MDX TopPercent Function Syntax

The basic syntax of the MDX TopPercent is:

TOPPERCENT (Set_Expression, Percentage, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Percentage: Please provide the percentage to want to retrieve.
Numeric_Expression: Any Multidimensional Expression or measure. The TopPercent function will use this Numeric_Expression to sort the data in descending order and then picks the number of records required to match the percentage.

In this article we will show you, How to write TopPercent function to extract the States from Geography Dimension (present in the Adventure Works Cube) who has contributed 60 percent of total sales in Reseller Sales amount. For this, we are going to use below shown data.

MDX TopPercent Function Example

The following query will return all the states present in the Geography Dimension who are contributing 60 Percent of total Reseller sales Amount.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
TOPPERCENT (
[Geography].[Geography].[State-Province], 60
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] column present in the geography Dimension on Rows. Next, TopPercent function will sort the data in the descending order using Measures.[Reseller Sales Amount] and then find the percentage of sales from total sales.


OUTPUT




If you observe the above screenshot, they are not the 60 Percent rows of Total records. If you total the Reseller Sales Amounts present in the above screenshot, it will be greater than or equal to 60 percent of the Total reseller Sales Amount. In short, above mentioned states are contributing 60 Percent Sales of total sales.
MDX Topcount Function Example 2


In this example, We are going to use two measures ([Measures].[Reseller Sales Amount] [Measures].[Reseller Gross Profit]).


CODE


SELECT {[Measures].[Reseller Sales Amount],
[Measures].[Reseller Gross Profit]} ON COLUMNS,
TOPPERCENT (
[Geography].[Geography].[State-Province], 60
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]


OUTPUT





If you observe the above screenshot, We are getting uneven results for [Reseller Gross Profit]. This is because data is sorted according to [Reseller Sales Amount]


NOTE: Please use the appropriate Measure as a third argument in the TOPPERCENT function otherwise, You will end up with wrong results.