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

In this article, we will clearly explain how to use the SUM function in Looker Studio and specific examples of its application. Let's utilize the functions available in Looker Studio to process and visualize data.

The SUM function is a familiar one also used in Excel and spreadsheets. While it cannot be used with metrics from connectors such as GA4 (Google Analytics 4), Search Console, and Google Ads, it can be utilized with certain dimensions and other data sources. Here, we will also explain how to produce totals without using calculated fields.

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

The SUM function is used to calculate the total of the values in a specified field.

Syntax

The syntax of the SUM function is as follows:

SUM("number")

"number" specifies the numeric values for which you want to obtain a total. The total of this numeric field will be calculated.

Conditions for Using the SUM Function

here are two conditions under which the SUM function can be used. Ensure that the numeric values for which you want to obtain a total meet the following two criteria:

Must be numeric

The SUM function is designed to calculate totals, so it cannot be used with dimensions that are not numeric.

The metric is not pre-aggregated

If the numerical values from the data source are already aggregated through connectors, etc., the SUM function cannot be used. Whether the values are pre-aggregated can be determined by the default aggregation of the data source being set to automatic. If set to automatic, the SUM function cannot be used, so please be careful.

Metrics from data sources using Looker Studio connectors such as GA4, Search Console, and Google Ads are all pre-aggregated, hence the COUNT function cannot be used on them. However, it can be used on dimensions.

(Quote)Looker Studio 

How to Use the SUM Function

To use the SUM 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 Total Sales in Looker Studio

In addition to creating calculated fields using the SUM function, there are multiple methods to visualize totals. Here, we will explain each method.

Method to Change the Default Aggregation of a Data Source

When you load data from a spreadsheet and create a data source in Looker Studio, Looker Studio automatically detects the data. However, you can manually change the default aggregation to total as follows:

(Quote)Looker Studio

By changing the default aggregation to total in this way, you can produce total values without using the SUM function.

How to Change the Aggregation Method in Reports

Next, we will introduce how to change the aggregation method in Looker Studio reports. First, click on the mark to the left of the metric for which you want to change the aggregation method.

(Quote)Looker Studio

Then, select the aggregation method you want to change. If you choose "Total," you can visualize the total value.

 (Quote)Looker Studio

 How to Use the SUM Function to Calculate Totals

Finally, we will explain how to calculate the total sales using the SUM function. First, create the following calculated field:

  (Quote)Looker Studio

1. Field Name: Please enter any field name.

2. Formula: SUM(Sales)

3. Save: Once you have entered the information, click Save.

A field named total_sales has now been created as shown below.

  (Quote)Looker Studio 

Next, since the field you just created is available in the reports, start adding it to the table. Add Total Sales to the dimensions as shown below.

 (Quote)Looker Studio 

In this way, commonly used totals can be represented in multiple ways, so please consider this as a reference. 

Relevant Looker Studio Official Documentation

Looker Studio Official Help : About calculated fields

Looker Studio Official Help : Function list