MDX TopCount Function

In Multidimensional Expression, Topcount function will sort the given data in the descending order and then selects the required number of records from the sorted data.

For example, If you want to find the Top 10 performing products or you want to find the top 10 regions with highest sales or to calculate Highest Salary we can use this Topcount function with measure.

Similarities between MDX TOPCOUNT and SQL TOP Clause

MDX TOPCOUNT is similar to SQL TOP Clause.

Like Top clause, Topcount extract the required number of records in the order they initially stored.
To extract Top 10 performing records we have to use Order BY Clause in SQL.
To extract Top 10 performing records we have to add third argument with appropriate Measure or Numeric Value.

MDX TopCount Function Syntax


The basic syntax of the MDX Topcount is:

TOPCOUNT (Set_Expression, Count, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Count: Please provide, How many number of records you want to retrieve.
Numeric_Expression: Any Multidimensional Expression or measure. This is purely optional argument. If we used this argument then TOPCOUNT will sort the data in descending order using this Numeric_Expression and then picks the number of records you asked for otherwise, TOPCOUNT will select the required number of records without sorting.

In this article we will show you, How to write TopCount function to extract Top 7 States from Geography table (present in the Adventure Works Cube) whose Reseller Sales amount is greater than others with example. For this, we are going to use below shown data.

MDX Topcount Function with Two arguments

In this example we will show you, What happen when we miss the third argument in the Topcount function. The following query will return first seven records from the above source in the order they appear above.


CODE


SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
TOPCOUNT (
[Geography].[Geography].[State-Province]
,7
) 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 table on Rows. Next, Topcount function will select the Top 7 records irrespective of their measure values.


OUTPUT




No one will accept the above result as top 7 performing states around the world.
MDX Topcount Function Example


In this example we will show you, What happen when we add the third argument in the Topcount function. The following query will sort the records in the descending order using [Measures].[Reseller Sales Amount] and then return first seven records from the sorted data. In short, We will get our top 7 performing states around the world.


CODE


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


OUTPUT

Using Multiple Measures in MDX Topcount Function

In this example we will show you, What happen when we add wrong measure as the third argument in the Topcount function. We are going to use two measures ([Measures].[Reseller Sales Amount], [Measures].[Reseller Gross Profit]) and we assign the [Measures].[Reseller Gross Profit] as sorting argument. The following query will sort the records in the descending order using [Measures].[Reseller Gross Profit] and then return first seven records from the sorted data.


CODE


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


OUTPUT





Although we are expecting Top 7 Sales, above query is retrieving Top 7 States Whose Gross Profit is Highest among the others.


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