MDX BottomSum Function

The MDX BottomSum function will sort the given data in the Ascending order and then selects the required number of records from the sorted data whose total is at least equal to specified Value.

TIP: Please use TOPSUM Function to calculate the Sum of Top N records

MDX BottomSum Function Syntax

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

BOTTOMSUM (Set_Expression, Value, Numeric_Expression)

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

In this article we will show you, How to write BottomSum function to extract the bottom 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 BottomSum Function Example

The following query will return Bottom States present in the Geography Dimension who are contributing Sale of 100000 of total Reseller sales Amount.

MDX CODE

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

OUTPUT

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

Using NON EMPTY in MDX BottomSum

In this example we are going to use Non Empty keyword to remove the NULL values returned by the BottomSum function.

MDX CODE

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
 NON EMPTY
  BOTTOMSUM (
	  [Geography].[Geography].[State-Province]
         ,100000
	 ,[Measures].[Reseller Sales Amount]
	 ) ON ROWS
FROM [Adventure Works]

OUTPUT

If you observe the above screenshot, MDX Non Empty keyword we used in the above query removed those Null records. Now, it looks Nice and Easy :)

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

Thank You for Visiting Our Blog