MDX BottomPercent Function

In Multidimensional Expression, BottomPercent 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 greater than or equal to specified Percentage.

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

The basic syntax of the MDX BottomPercent is:

BOTTOMPERCENT (Set_Expression, Percentage, Numeric_Expression)

Set_Expression: Any Multidimensional Expression or attributes on which you want check.
Percentage: Please provide the percentage to want to retrieve.
Numeric_Expression: Any Multidimensional Expression or measure. The 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.

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

MDX BottomPercent Function Example

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


CODE


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, BottomPercent function will sort the data in the Ascending order using Measures.[Reseller Sales Amount] and then find the percentage of sales from total sales.


OUTPUT





If you observe the above 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, above mentioned states 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]).


CODE


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]


OUTPUT





If you observe the above screenshot, We are getting uneven results for [Reseller Gross Profit]. This is because data is 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 wrong results.
Using NON EMPTY along with MDX BottomPercent 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
BOTTOMPERCENT (
[Geography].[Geography].[State-Province], 1
,[Measures].[Reseller Sales Amount]
) ON ROWS
FROM [Adventure Works]


OUTPUT





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