/    /  Amazon QuickSight – percentileContOver

Amazon QuickSight – percentileContOver

 

The percentileContOver function calculates the percentile based on the actual values in the measure and uses the sorting and grouping applied in the field wells. The resulting values are partitioned by the specified dimension at the specified calculation level. This function is useful for determining which data points are present in a given percentile.

 

To get the nearest percentile value present in your dataset, use percentileDiscOver. To get an exact percentile value that might not be present in your dataset, use percentileContOver instead.

 

Syntax

#Start#
percentileContOver (
measure,
percentile-n,
[partition-by, …],
calculation-level
)
#End#

 

This function takes the following arguments:

 

  • measure: A numeric value used to compute the percentile. It must be a measure or metric, and nulls are ignored in the calculation.
  • percentile-n: The percentile value can be any numeric constant between 0 and 100. A percentile value of 50 computes the median value of the measure.
  • partition-by (optional): One or more dimensions to partition the result by. Enclose each field in curly braces if it has more than one word, and enclose the entire list in square brackets.
  • calculation-level: Specifies where to perform the calculation in relation to the order of evaluation. There are three supported calculation levels: PRE_FILTER, PRE_AGG, and POST_AGG_FILTER.

 

 

Suppose you have a dataset of sales records for a retail company, with columns for the product name, sales amount, and region where the sales occurred. You want to find the 90th percentile of sales amounts for each product, partitioned by region.

 

You can use the following formula to calculate the 90th percentile of sales amounts for each product, partitioned by region, using the percentileDiscOver function:

 

Example

#Start#
percentileDiscOver(
    {sales_amount},
    90,
    [{product_name}, {region}],
    POST_AGG_FILTER
)
#End#

 

Here, {sales_amount} is the name of the measure containing the sales amounts, 90 specifies the percentile you want to calculate, [{product_name}, {region}] specifies the dimensions to partition the data by, and POST_AGG_FILTER specifies the calculation level.

 

This formula will return the actual sales amount that corresponds to the 90th percentile for each product, within each region. For example, if the 90th percentile sales amount for a particular product in a region is $10,000, the formula will return that value.