MDX BottomSum Function

In Multidimensional Expression, 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 MDX BottomSum is:

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.


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 Function

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


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 BOITTOMSUM function otherwise, You will end up with wrong results.