GROUPPERCENTILE

Syntax

GROUPPERCENTILE(<number>; [<number>])

Description

Return the number which is nth percentile in this distribution as float. The value returned by this function identifies the point where N% of the samples of the distribution are smaller than the Nth percentile. The first argument is the data column, the second argument is N (a value between 0 and 100).

This function calculates this value by first counting the total number of values in the group and using the nth percent of that as a positioning value. Finally, the function orders all values from lowest to highest, and returns the value that is in the position calculated earlier.

The position is calculated using the following formula:

Position = ((N/100) * (number of values -1)) +1

The percentile is then calculated using the following formula if the position isn't a whole number:

Percentile = value of rounded down position + the decimal value of the position * (value of rounded up position - value of rounded down position)

This is an aggregate function.

Example

Given the following data:

First create a group using GROUPBY(#RawData!Groups). This groups the values of group1 and group2 together in order to figure out the percentile. 

Then use the GROUPPERCENTILE(#RawData!Participants;80) function, and the result is a float that is the nth percentile (in this case 80th percentile) in relation to the GROUPBY() column. In this example, the function calculates the value at which 80 percent of the values in the data column are below that number and 20 percent of the values in that data column are above. 

For example, group1's percentile is calculated by first counting the number of values, which is 9, which then calculates position value using the positioning formula:

Position(80%) = (80/100 * 9 - 1) + 1 = 7.4 

The resulting number, 7.4, is used as a placement marker. All of the values are arranged in order from lowest to highest (5,5,5,5,6,7,8,9,12), and GROUPPERCENTILE returns the value that is in the 7.4th position using the percentile formula:

Percentile(7.4) = 8 + 0.4 * (9 - 8) = 8.4