MDX TOPSUM Function

The MDX TopSum function will sort the given data in the descending order. Then selects the required number of records from the sorted data whose total is at least equal to the specified value.

MDX TopSum Function Syntax

The basic syntax of the MDX TopSum in Multidimensional Expression is as shown below:

TOPSUM (Set_Expression, Value, Numeric_Expression)
  • Set_Expression: Any Multidimensional Expression or attributes on which you want to check.
  • Value: Please provide the Value. The MDX TOPSUM function will retrieve the records whose total is at least this Value.
  • Numeric_Expression: Any Multidimensional Expression or measure. The TopSum function will use this Numeric_Expression to sort the data in descending order and then picks the number of records required to match the Value.

Write the MDX TopSum function to extract the States from Geography Dimension (present in the Adventure Works Cube), whose total is at least equal to the user given Value. For this, we are going to use the below-shown data.

MDX TOPSUM FUNCTION

MDX TopSum Function Example

The following MDX TOPSUM query returns all the states present in the Geography Dimension, who are contributing Sale of 40000000 of total Reseller Sales Amount.

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
 TOPSUM (
	 [Geography].[Geography].[State-Province], 40000000
	,[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, TopSum function will sort the data in the descending order using Measures.[Reseller Sales Amount] and then add the Sales amount of each state until it reaches 40000000.

MDX TOPSUM FUNCTION 1

If you observe the above screenshot, If you total the Sales amount of above-displayed states, they approximately equal to 40243296.5. It means greater than what we required.

MDX TopSum Function Example 2

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

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

In the above MDX TOP SUM Query, We selected two Measures ([Reseller Sales Amount], [Reseller Gross Profit]) on the Columns, and [State Province] attribute present in the geography Dimension on Rows. Next, TopSum function will sort the data in the descending order using Measures.[Reseller Gross Profit] and then add the Sales amount of each state until it reaches 500000.

MDX TOPSUM FUNCTION 2

If you observe the above TOPSUM screenshot, We are getting the top states whose [Reseller Gross Profit] total is 500000.

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

Categories MDX