MDX BottomCount Function

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

For example, If you want to find the Bottom 10 performing products or you want to find the 10 regions with Lowest sales or to calculate Lowest Salary we can use this BottomCount function with measure.

TIP: Please use TOPCOUNT Function to get Top 10 Performing States.


MDX BottomCount Function Syntax

The basic syntax of the MDX BottomCount is:

BOTTOMCOUNT (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 BOTTOMCOUNT will sort the data in Ascending order using this Numeric_Expression and then picks the number of records you asked for otherwise, BOTTOMCOUNT will select the required number of records without sorting.

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

MDX BottomCount Function with Two arguments

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


CODE

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

In the above MDX Query, We selected the [Reseller Sales Amount] Measure on the Columns and [State Province] attribute present in the geography Dimension on Rows. Next, BottomCount function will select the Bottom 10 records irrespective of their measure values.


OUTPUT




From the above screenshot you can observe that, they are not the Bottom 10 states whose Reseller Sales Amount is lower than other States. Here BottomCount function just selected bottom 10 records in the order they inserted.
MDX BottomCount Function Example

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


CODE

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


OUTPUT

Using NON EMPTY along with MDX BottomCount 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
BOTTOMCOUNT (
[Geography].[Geography].[State-Province], 10
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]


OUTPUT




Although we asked for bottom 10 records, above query is displaying 3 records because remaining 7 records are NULLS. MDX Non Empty keyword we used in the above query removed those 7 records.

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