BottomPercent

The MDX BottomPercent function for Essbase returns the smallest possible subset of a set for which the total results of a numeric evaluation are at least a given percentage. The result set is returned with elements listed from smallest to largest.

Syntax

BottomPercent ( set, percentage, numeric_value_expression )

Parameters

set: The set from which the bottom-percentile elements are selected.
percentage: The percentile. This argument must be a value between 0 and 100.
numeric_value_expression: The expression that defines the selection criteria (see MDX Grammar Rules).

Notes

This function ignores negative and missing values.

Example

The following query returns data for products making up the lowest 5th percentile of all product sales in the Sample Basic database.

WITH
 SET [Lowest 5% products] AS
  'BottomPercent (
   { [Product].members },
   5,
   ([Measures].[Sales], [Year].[Qtr2])
   )'

MEMBER 
 [Product].[Sum of all lowest prods] AS
 'Sum ( [Lowest 5% products] )'

MEMBER [Product].[Percent that lowest sellers hold of all product sales] AS
 'Sum ( [Lowest 5% products] ) / [Product] '

SELECT
 {[Year].[Qtr2].children}
on columns,
 {
  [Lowest 5% products],
  [Product].[Sum of all lowest prods],
  [Product],
  [Product].[Percent that lowest sellers hold of all product sales]
 }
on rows
FROM Sample.Basic
WHERE ([Measures].[Sales])

In the WITH section,

The named set [Lowest 5% products] consists of those products accounting for the lowest 5 percent of sales in the second quarter. This set includes Birch Beer, Caffeine Free Cola, Strawberry, Sasparilla, and Vanilla Cream.
The first calculated member, [Product].[Sum of all lowest prods], is used to show the sum of the sales of the products with sales in the lowest fifth percentile.
The second calculated member, [Product].[Percent that lowest sellers hold of all product sales], is used to show, for each month, how the sales of lowest-selling products compare (as a percentage) to sales of all products in the Product dimension.

This query returns the following grid:

Table 4-41 Output Grid from MDX Example

(axis)	Apr	May	Jun
Birch Beer	954	917	1051
Caffeine Free Cola	1049	1065	1068
Strawberry	1314	1332	1316
Sarsaparilla	1509	1552	1501
Vanilla Cream	1493	1533	1612
Sum of all lowest prods	6319	6399	6548
Product	32917	33674	35088
Percent that lowest sellers hold of all product sales	0.192	0.194	0.187