Some of the explores include a set of pre-selected percentile metrics in the Measures section.
These percentile metrics return a value representing the x percentile metric of the set of records returned by the query. For example, specifying the 75th percentile will return the value that is greater than 75% of the values in the dataset.
Note: In the Model Point Results table, the percentile returns a percentile value based on the Value After Investment column (not the value before investment).
To identify the value to return, Looker calculates the total number of data values returned by the query parameters and multiplies the specified percentile times the total number of data values. Regardless of how the data is actually sorted, Looker identifies the data values’ relative order in increasing value. The data value that Looker returns depends on whether the calculation results in an integer or not, as discussed below.
If the Calculated Value Is Not an Integer
Looker rounds the calculated value up and uses it to identify the data value to return. In this example set of 19 test scores, the 75th percentile would be identified by 19 * .75 = 14.25, which means that 75% of the values are in the first 14 data values — below the 15th position. Thus, Looker returns the 15th data value (87) as being larger than 75% of the data values.
If the Calculated Value Is an Integer
In this slightly more complex case, Looker returns an average of the data value at that position and the following data value. To understand this, consider a set of 20 values the 75th percentile would be identified by 20 * .75 = 15, which means that the data value at the 15th position is part of the 75th percentile and we need to return a value that is above 75% of the data values. By returning the average of the values at the 15th position (82) and the 16th position (87), Looker ensures that 75%. That average (84.5) does not exist in the set of data values but would be larger than 75% of the data values.