MDX BottomCount Function

The MDX 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 the Lowest sales or to calculate Lowest Salary. We can use this MDX BottomCount function with a measure.

MDX BottomCount Function Syntax

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

BOTTOMCOUNT (Set_Expression, Count, Numeric_Expression)
  • Set_Expression: Any Multidimensional Expression or attributes on which you want to check.
  • Count: How many numbers of records you want to retrieve?.
  • Numeric_Expression: Any Multidimensional Expression or measure. It is an optional argument. If we used this argument, the MDX BOTTOMCOUNT would 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.

How to write MDX 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 the below-shown data.

MDX BOTTOMCOUNT FUNCTION

MDX BottomCount with Two arguments

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

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

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, the BottomCount function will select the Bottom 10 records irrespective of their measured values.

MDX BOTTOMCOUNT FUNCTION 1

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

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

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

NON EMPTY along with MDX BottomCount Function

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

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

Although we asked for the bottom 10 records, the above query is displaying 3 records because the 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 end up with the wrong results.

Categories MDX