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 )

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: