The MDX TopSum 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 at least equal to specified Value.

### MDX TopSum Function Syntax

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

1 |
TOPSUM (Set_Expression, Value, Numeric_Expression) |

**Set_Expression:**Any Multidimensional Expression or attributes on which you want check.**Value:**Please provide the Value. 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

In this article we will show you, How to write 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 below shown data.

## MDX TopSum Function Example

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

**MDX CODE**

1 2 3 4 5 6 |
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.

**OUTPUT**

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]).

**MDX CODE**

1 2 3 4 5 6 7 |
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 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.

**OUTPUT**

If you observe the above 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 wrong results.

Thank You for Visiting Our Blog

## Leave a Reply