[Looker Studio] How to Use the PERCENTILE Function and Practical Examples | Calculated Fields

May 23, 2024

[Looker Studio] How to Use the PERCENTILE Function and Practical Examples | Calculated Fields

In this article, we will explain how to use the PERCENTILE function in Looker Studio and provide specific examples of its application. By leveraging the functions available in Looker Studio, you can process and visualize your data effectively.

The PERCENTILE function cannot be used with GA4 (Google Analytics 4), Search Console, or Google Ads through connectors, but it can be used with data imported from spreadsheets.

What are Calculated Fields in Looker Studio?

Calculated Fields in Looker Studio are a handy feature that allows you to create custom fields by using operators (such as addition, subtraction, multiplication, division), functions, and regular expressions based on existing items for use in reports.

Also, for basic usage of Looker Studio, please refer to the "How to Use Looker Studio" guide. Looker Studio is a very convenient tool that is free to use and allows for the creation of easy-to-understand reports by connecting to various data sources, so let's make active use of it.

What is the PERCENTILE Function?

The PERCENTILE function is used to calculate the percentile value within a specified dataset. A percentile is a measure that indicates the value below which a given percentage of data points in a dataset fall. For example, the 75th percentile means that 75% of the values in the dataset are below this value. Therefore, the 50th percentile is the same as the median.

Percentiles are useful for understanding the distribution of data and for performing comparative analyses.

Syntax

The syntax of the PERCENTILE function is as follows:

PERCENTILE("Target Numeric Field", "Percentile")

The "Target Numeric Field" specifies the numeric value for which you want to obtain the percentile.

For "Percentile" enter a number between 0 and 100. A value of 0 will retrieve the minimum value, 50 will retrieve the median, and 100 will retrieve the maximum value.

Conditions for Using the PERCENTILE Function

There are two conditions under which the PERCENTILE function can be used. Before attempting to retrieve percentiles, ensure that the numerical values in question do not fall into either of the following categories:

Must be Numerical Values

The PERCENTILE function is designed to operate on numerical values. It cannot be applied to non-numeric dimensions.

Must Not be Aggregated Metrics

If the numerical values in the data source have already been aggregated by connectors or other means, the PERCENTILE function cannot be applied. To determine if the values are aggregated, check if the default aggregation setting of the data source is set to automatic. If it is set to automatic, the PERCENTILE function cannot be used, so please be aware of this limitation.

(Quote:Looker Studio)

How to Use the PERCENTILE Function

To use the PERCENTILE function, you need to create a calculated field in Looker Studio. There are two types of calculated fields: data source calculated fields and chart-specific calculated fields. This article will focus on creating a data source calculated field, but for more detailed differences between the two, please see the differences between data source calculated fields and chart-specific calculated fields.

As previously mentioned, the SUM function cannot be used with metrics from data sources such as GA4 (Google Analytics 4), Search Console, and Google Ads that utilize connectors. Therefore, for this example, we have prepared sample data in a spreadsheet containing daily customer and sales data for each store. I will use this sample data to explain.

Use Case: Visualizing the 75th percentile of sales in Looker Studio

To set up the calculated field as follows.

(Quote:Looker Studio)

① Field Name: Please enter any field name.

② Formula:PERCENTILE(sales,75)

③ Save: Once you have completed entering the information, click save.

A field called the sales 75th percentile has been created as follows.

(Quote:Looker Studio)

Next, let's add the previously created metric to ensure it is available in the report. By doing this, you will see that the numbers are correctly reflected in the table.

Since the 75th percentile of sales is 57,500, it means that 75% of the total sales data is below 57,500.