BottomPercent

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 )

set The set from which to select the bottom-percentile elements.
percentage The percentile. This argument must be a value between 0 and 100.
numeric_value_expression The expression that defines the selection criteria.

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,

This query returns the following grid:

Apr May Jun
Birch Beer 954 917 1051
Caffeine Free Cola 1049 1065 1068
Strawberry 1314 1332 1316
Sasparilla 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

©2004 Hyperion Solutions Corporation. All Rights Reserved.
http://www.hyperion.com