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

July 17, 2024

In this article, we will provide a clear explanation of how to use the AVG function in Looker Studio, along with practical examples of its application. By leveraging the functions available in Looker Studio, you can process your data and visualize the information you need.

Additionally, while the AVG function cannot be used with GA4 (Google Analytics 4), Search Console, or Google Ads connectors, we will also discuss alternative methods to display average values for these data sources.

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 AVG Function?

The AVG function is used to calculate the "average." It is applied to metrics that contain numerical data and computes the average value of all numbers within the selected range. This function is often used when you want to understand the average performance from a large set of data, and it helps in understanding the overall trend of specific metrics.

Syntax

The syntax for the AVG function is as follows:

AVG("number")

The "number" specifies the numerical value for which you want to obtain the average. This function will calculate the average of the specified numerical value.

Conditions for Using the AVG Function

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

(Quote:Looker Studio)

How to Use the AVG Function

To use the AVG 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 mentioned earlier, the AVG function cannot be used with data sources such as GA4 (Google Analytics 4), Search Console, and Google Ads when using connectors. Therefore, we have prepared sample data that includes the number of customers and sales data per store by date. We will explain using this sample data.

Use case:Visualize the average sales per day for each store using Looker Studio.

To set up the calculated field as follows.

(Quote:Looker Studio)

① Field Name: Please enter any field name.

② Formula:AVG(sales)

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

A field called the sales_avg 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)

How to Find the AVG Value Without Using Calculated Fields

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

Use case:Visualize the average customers per day for each store using Looker Studio.

First, click on the mark to the left of the metric for which you want to calculate the average in the report.

(Quote:Looker Studio)

Next, enter the name and check the box for the average aggregation method.

(Quote:Looker Studio)

The settings have been applied to the report, and the average sales are now displayed. This concludes the method of displaying averages using the AVG function. Please give it a try.

How to Display the Average Values of Metrics in GA4

Finally, we will explain how to display averages in GA4 data using a Looker Studio connector that does not support the AVG function.

Use case: Display the average number of sessions per day

The following is how to set up calculated fields.

(Quote:Looker Studio)

The average number of sessions per day is calculated by dividing the total number of sessions by the number of days. The key point here is that the date is a dimension, not a numeric value, so it cannot be directly divided. Therefore, we use the COUNT_DISTINCT function to count the unique dates and convert them into a numeric value.