/    /  Amazon QuickSight – percentileDiscOver

Amazon QuickSight – percentileDiscOver

 

The percentileDiscOver function is used to calculate the percentile based on actual values in the measure field. It utilizes the grouping and sorting applied in the field wells and partitions the result by the specified dimension at the given calculation level. This function is also known as percentileOver.

 

To determine which actual data points are present in a specific percentile, use percentileDiscOver. If you need to return the nearest percentile value present in your dataset, use this function. On the other hand, if you require an exact percentile value that may not be present in your dataset, use percentileContOver.

 

Syntax

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

 

This function takes the following arguments:

 

  • measure: Specifies a numeric value that the function uses to compute the percentile. The argument must be a measure or metric. Null values are ignored in the calculation.
  • percentile-n: The percentile value can be any numeric constant from 0 to 100. A percentile value of 50 computes the median value of the measure.
  • partition-by: (Optional) One or more dimensions you want to partition by, separated by commas. If a field has more than one word, enclose each field in { } (curly braces). The entire list is enclosed in [ ] (square brackets).
  • calculation-level: Specifies where to perform the calculation in relation to the order of evaluation. Three supported calculation levels include:
  • PRE_FILTER
  • PRE_AGG
  • POST_AGG_FILTER (default) – To use this calculation level, you must specify an aggregation on the measure field, for example, sum(measure).

 

PRE_FILTER and PRE_AGG are applied before the aggregation occurs in a visualization. For these two calculation levels, you cannot specify an aggregation on the measure field in the calculated field expression. For more information on calculation levels and when they apply, see Order of evaluation in Amazon QuickSight and Using level-aware calculations in Amazon QuickSight.

Suppose we have a dataset that contains information about the salaries of employees in a company. We want to find the 90th percentile salary for each department. We can use the percentileDiscOver function to calculate this value.

 

Example

#Start#
percentileDiscOver(salary, 90, [department], post_agg_filter)
#End#

 

Here, “salary” is the measure that we want to use to calculate the percentile, “90” is the percentile value we want to calculate, “[department]” is the dimension we want to partition the data by, and “post_agg_filter” is the calculation level we want to use.

 

This function would return the 90th percentile salary for each department in the dataset.