MDX BottomPercent Function

The MDX BottomPercent function will sort the given data in the Ascending order. Then MDX bottompercent selects the required number of records from the sorted data whose total is greater than or equal to the specified Percentage.

MDX BottomPercent Function Syntax

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

BOTTOMPERCENT (Set_Expression, Percentage, Numeric_Expression)
  • Set_Expression: Any Multidimensional Expression or attributes on which you want to check.
  • Percentage: Please provide the percentage to want to retrieve.
  • Numeric_Expression: Any Multidimensional Expression or measure. The MDX BottomPercent function will use this Numeric_Expression to sort the data in Ascending order and then picks the number of records required to match the percentage.

How to write MDX BottomPercent function to extract the States from Geography Dimension (present in the Adventure Works Cube) who are at the bottom 1 percent of total sales in Reseller Sales amount?. For this, we are going to use the below-shown data.

MDX BOTTOMPERCENT FUNCTION

MDX BottomPercent Function Example

The following query returns all the states present in the Geography Dimension who are contributing Bottom 1 Percent of total Reseller sales Amount.

TIP: Please use the MDX TopPercent Function to find the Top percentage.

SELECT {[Measures].[Reseller Sales Amount]} ON COLUMNS,
 BOTTOMPERCENT (
	    [Geography].[Geography].[State-Province], 1
            ,[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, the MDX BottomPercent function will sort the data in the Ascending order using Measures.[Reseller Sales Amount]. And then, it finds the percentage of sales from total sales.

MDX BOTTOMPERCENT FUNCTION 1

If you observe the above bottompercent screenshot, they are not the bottom 1 Percent rows of Total records. If you total the Reseller Sales Amounts present in the above screenshot, it will be greater than or equal to 1 percent of the Total Reseller Sales Amount. In short, the states mentioned above are the bottom states who are contributing bottom 1 Percent Sales of total sales.

MDX BottomPercent Function Example 2

In this example, We are going to use two measures ([Measures].[Reseller Sales Amount] [Measures].[Reseller Gross Profit]).

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

If you observe the above screenshot, We are getting uneven results for [Reseller Gross Profit]. It is because data sorted according to [Reseller Sales Amount]

NOTE: Please use the appropriate Measure as a third argument in the BOTTOMPERCENT function. Otherwise, you will end up with the wrong results.

NON EMPTY along with MDX BottomPercent Function

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

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

Although we asked for bottom 1 Percent records, the above query is displaying few records because the remaining records are NULLS. MDX Non Empty keyword we used in the above query removed those Null records.

Categories MDX