percentileDiscOver - Amazon QuickSight

# percentileDiscOver

The `percentileDiscOver` function calculates the percentile based on the actual numbers in `measure`. It uses the grouping and sorting that are applied in the field wells. The result is partitioned by the specified dimension at the specified calculation level. The `percentileOver` function is an alias of `percentileDiscOver`.

Use this function to answer the following question: Which actual data points are present in this percentile? To return the nearest percentile value that is present in your dataset, use `percentileDiscOver`. To return an exact percentile value that might not be present in your dataset, use `percentileContOver` instead.

## Syntax

``````percentileDiscOver (
measure
, percentile-n
, [partition-by, …]
, calculation-level
)``````

## Arguments

measure

Specifies a numeric value to use to compute the percentile. The argument must be a measure or metric. Nulls are ignored in the calculation.

percentile-n

The percentile value can be any numeric constant 0–100. A percentile value of 50 computes the median value of the measure.

partition-by

(Optional) One or more dimensions that you want to partition by, separated by commas. Each field in the list is enclosed in { } (curly braces), if it is more than one word. The entire list is enclosed 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

• POST_AGG_FILTER (default) - To use this calculation level, you need to specify an aggregation on `measure`, for example `sum(measure)`.

PRE_FILTER and PRE_AGG are applied before the aggregation occurs in a visualization. For these two calculation levels, you can't specify an aggregation on `measure` in the calculated field expression. To learn more about calculation levels and when they apply, see Order of Evaluation in Amazon QuickSight and Using Level-Aware Aggregations.

## Returns

The result of the function is a number.

## Example of percentileDiscOver

The following example helps explain how percentileDiscOver works.

Example `Comparing calculation levels for the median`

The following example shows the median for a dimension (category) by using different calculation levels with the `percentileDiscOver` function. The percentile is 50. The dataset is filtered by a region field. The code for each calculated field is as follows:

• `example = left( category, 1 )` (A simplified example.)

• `pre_agg = percentileDiscOver ( {Revenue} , 50 , [ example ] , PRE_AGG)`

• `pre_filter = percentileDiscOver ( {Revenue} , 50 , [ example ] , PRE_FILTER) `

• `post_agg_filter = percentileDiscOver ( sum ( {Revenue} ) , 50 , [ example ], POST_AGG_FILTER )`

``````example   pre_filter     pre_agg      post_agg_filter
------------------------------------------------------
0            106,728     119,667            4,117,579
1            102,898      95,946            2,307,547
2             97,629      92,046              554,570
3            100,867     112,585            2,709,057
4             96,416      96,649            3,598,358
5            106,293      97,296            1,875,648
6             97,118      64,395            1,320,672
7             99,915      90,557              969,807``````

Example The median

The following example calculates the median (the 50th percentile) of `Sales` partitioned by `City` and `State`.

``````percentileDiscOver
(
Sales,
50,
[City, State]
) ``````

The following example calculates the 98th percentile of ```sum({Billed Amount})``` partitioned by `Customer Region`. The fields in the table calculation are in the field wells of the visual.

``````percentileDiscOver
(
sum({Billed Amount}),
98,
[{Customer Region}]
) ``````

The following screenshot shows the how these two examples looks on a chart. 