Looker Studio Function: PERCENTILE

Category: Statistical function

The PERCENTILE function in Looker Studio calculates the value below which a given percentage of observations fall in a dataset. It helps identify the value at a specific percentile rank (e.g., the 90th percentile) of the data distribution.

Purpose of PERCENTILE Function:

1. Data Distribution Analysis: It helps in identifying key data points that fall below or above a given percentile, aiding in the analysis of data distributions.
2. Outlier Detection: By calculating percentiles, you can easily spot outliers by identifying values that lie significantly above or below the standard percentiles (like the 90th or 95th percentiles).
3. Decision Making: Percentiles assist in decision-making processes where certain thresholds or cut-off points are required, such as evaluating performance benchmarks or targeting specific customer segments.

Type of Calculation:

The PERCENTILE function is typically a scalar calculation, which means it computes a single value for a given percentile based on a dataset. It operates by sorting the data and returning the value that corresponds to a specific percentile.

Practical Use Cases:

1. Performance Benchmarks: Use it to calculate performance metrics, such as determining the 90th percentile of sales, to measure the top-performing sales representatives.
2. Customer Segmentation: It can be used in market research to identify high-value customers based on their expenditure levels, such as calculating the 95th percentile of customer spending.
3. Time Series Analysis: Use percentiles to analyze time-based data, such as identifying the 50th percentile of website traffic over a month, to measure average user activity.

How Does the Percentile Function Work?

1. Data is sorted in ascending order.
   
   
2. A percentile rank (e.g., 0.9 for 90th percentile) is applied.
   
   
3. The function returns the value at that percentile, interpolating between two values if needed.

What Does It Run?

The function runs on numeric fields within a specified data context or aggregation scope (like date range, dimension grouping, or filters applied in the report).

When Should We Use It?

Use Percentile when:

• You want to understand how values are distributed.

• Segmenting data into percent-based groups (quartiles, deciles).

• Identifying outliers or thresholds (e.g., top 1% or bottom 25%).

Example:

Using PERCENTILE(Monthly Sales, 0.8)
Result: 9,300 → meaning 80% of employees have sales below or equal to $9,300.

Basic Usage

Returns the 90th percentile value of the Sales field.

Column Usage

Apply the Percentile function directly in a calculated field or custom metric in Looker Studio. It works on numeric fields aggregated over dimensions like date, category, or region.

Example:

This shows the 75th percentile of revenue across a dimension like Country or Product Line.

Handling Decimals and Scientific Notation

• Use decimal values between 0 and 1 to define the percentile (e.g., 0.25 for 25th percentile).

• Large or small numbers might be displayed in scientific notation (e.g., 1.2E+5). Use formatting options to convert them to readable formats via Field Format Settings.

Combining with Other Functions

Percentile can be combined with functions like:

• IF(): to filter or flag outliers.

• AVG(), MEDIAN(): for comparison.

• CASE WHEN: to segment users into performance tiers.
  
  

Tips and Tricks

• Use Filters to apply percentile on specific groups (e.g., region-wise).

• Avoid over-aggregation: Percentile is best used on raw data or appropriately scoped dimensions.

• Use dimension groupings like “Week” or “Product Category” to get contextual percentile values.

• For large datasets, expect percentile results to be interpolated rather than exact.