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

June 30, 2024

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

In this article, we will clearly explain how to use the MEDIAN function in Looker Studio and provide specific examples of its application. By utilizing the functions available in Looker Studio, let's process and visualize the data.

The MEDIAN function cannot be used with GA4 (Google Analytics 4), Search Console, and Google Ads when using 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 MEDIAN Function?

The MEDIAN function is used to obtain the "median" of a specified numeric field. It is applied to fields containing numeric data and calculates the median from all numbers within the selected range. The median is the value that lies exactly in the middle when the data is arranged in ascending order. Unlike the mean, it is less affected by extreme values and helps to understand the distribution of the data.

For example, if the test scores of a class are [55, 60, 65, 70, 1000], the mean would be (55 + 60 + 65 + 70 + 1000) / 5 = 250. However, this data set includes an extremely high value (1000). In this case, the mean is not an appropriate representation of the data. On the other hand, the median is 65, which more appropriately reflects the central value of the class's test scores.

Syntax

The syntax for the MEDIAN function is as follows:

MEDIAN(“number”)

"Number" specifies the value for which you want to obtain the median.

Conditions for Using the MEDIAN Function

There are two conditions under which the MEDIAN 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 MEDIAN 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 MEDIAN 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 MEDIAN function cannot be used, so please be aware of this limitation.

(Quote:Looker Studio)

How to Use the MEDIAN Function

To use the MEDIAN 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 MEDIAN 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 Median Sales in Looker Studio

To set up the calculated field as follows.

(Quote)Looker Studio

① Field Name: Please enter any field name.

② Formula:MEDIAN(sales)

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

A field called the sales_median has been created as follows.

(Quote)Looker Studio

Next, let's add the metrics we created earlier to the report so that they can be utilized. By doing so, you will see that the numbers are correctly reflected in the table.

(Quote)Looker Studio

By comparing the average sales created using the AVG function ,it is also helpful to understand sales deviations.

(Quote)Looker Studio

How to Find the MEDIAN Value Without Using Calculated Fields

For relatively simple calculations, such as finding the median value, you can reflect them in the report without using calculated fields. Here is an explanation of the procedure.

Use case: Visualizing the Median Number of Customers in Looker Studio

(Quote)Looker Studio

Enter the name, then check the box for the median aggregation method.

lookerstudio-median-report-metric-setting

(Quote)Looker Studio

Now, the settings have been applied to the report, and the median number of customers is displayed. This is the method for displaying the median using the MEDIAN function. Please give it a try.