How to use the SUM function in Looker Studio

How to use the SUM function in Looker Studio

The SUM function is one of the most commonly used functions in Looker Studio (formerly Google Data Studio). It allows you to add up values across rows or columns in your data set. This handy function can help you quickly calculate totals, sums, and aggregates.

Aggregation is a key functionality in Looker Studio

Aggregating and summarizing data is an essential part of data analysis. One of the most common aggregation functions used is SUM - calculating totals by adding up values. In Looker Studio, the SUM function allows you to easily total up fields in your data to gain insightful metrics.

There are numerous aggregation functions to use in Looker Studio such as APPROX_COUNT_DISTINCT, AVG, COUNT, COUNT_DISTINCT, MAX, MEDIAN, MIN, PERCENTILE, STDDEV and VARIANCE. They are at the basis of any mathematical and/or statistical computations. Let’s explore what the SUM function is, how to use it to create calculated fields, and some real-world examples of using SUM to gain business insights.

What is the SUM function?

The SUM function is a mathematical function that returns the sum of all values in a set of numbers. In Looker Studio, you can use the SUM function to create calculated fields that calculate the total value of a set of data. Whether you need to total sales, count customers, or sum up any group of numbers - the SUM function is a powerful tool for your Looker Studio dashboard. Let’s learn how to harness the flexibility of SUM to create aggregated metrics that help drive informed decisions.

What is SUM function good for?

The SUM function can be used in a variety of real-world use cases. For example, you could use the SUM function to:

- Calculate the total sales for a given period of time.
- Calculate the total number of customers who have purchased a product.
- Calculate the total revenue generated by a specific marketing campaign.
- Calculate a sum for a page or an entire report.

The SUM function is a powerful tool that can be used to calculate the total value of a set of data. In Looker Studio, you can use the SUM function to create calculated fields that provide insights into your data.

The easiest syntax to learn in Looker Studio

The SUM function allows you to calculate totals by summing values from a field. The formula for the SUM function is simple:


Note that field_name cannot be an aggregated field or the result of an aggregation function.

If you want to filter your results, like when you are using a WHERE statement in traditional SQL, you have to add a filter to the entire report or to a page. Use select File => Report settings for the first option, Page => Current page settings for the second. The report manager lets you create powerful filters to only SUM the pertinent data. Created filters can be saved and reused. It is far more simple and intuitive than to code everything by hand.

A few key things worth mentioning when working with SUM :

- SUM works on numeric fields only. Using on strings or booleans may result in errors.
- You can sum only one field at once: SUM(Revenue).
- Use filters to sum subsets of data by using the filter manager.
- SUM cannot be nested to calculate sums of sums for example.

The SUM function provides flexible aggregation for totaling numbers in your Looker Studio analysis. It has a simple syntax but allows you to build professional and robust metrics.

Let’s tackle a real-world example

Imagine you work for an e-commerce company and have a database of orders data. Some of the fields are:

- Order ID - Unique identifier for each order
- Order Date - Date the order was placed
- Customer ID - ID representing the customer
- Order Total - Total dollar amount of the order

Your manager asks you to create a Looker Studio dashboard showing monthly total revenue over the past year, broken down by customer segment (new vs returning). Here is how you could use the SUM function to calculate the totals:

  1. Create a calculated field called 'Revenue' using the formula: SUM(Order Total) This sums the order amounts.
  2. Add a Month and Year extraction from the Order Date field.
  3. Create a parameter for Customer Type with values "New" and "Returning".
  4. Add a filter Customer Type = $Customer Type.
  5. Visualize the Revenue aggregated by Month and Year, filtered by Customer Type parameter.

Now your dashboard can show monthly total revenue for new vs returning customers. The SUM function allowed you to flexibly aggregate order totals for analysis. You could extend this by adding filters with the filter manager for product categories, date ranges, etc. The key is SUM enables you to easily total metrics for insightful reporting.

Key benefits

Here are some key takeaways on using the SUM function in Looker Studio:

- SUM allows flexible aggregation of data by totalling field values.
- Use filters from filter manager to selectively sum subsets of data.
- Powerful for gaining insights through totaling metrics like revenue, sales, counts.
- Create interactive summaries using parameters in filters
- Filters are reusable one may fit many of your SUM use cases.
- Simple syntax but allows building robust aggregated metrics.
- Essential Lookup Studio function for summarizing and analyzing data.

In summary, the SUM function is invaluable for totaling and digesting numbers in your data. It has many flexible applications to gain actionable business insights.

More function to use with Looker Studio

Unleashing the Power of the WEEKDAY Function in Looker Studio: A Comprehensive Guide to Data Analysis Based on Weekdays
Mastering the Use of HYPERLINK Function in Looker Studio for More Interactive Data Representation
Using the UPPER Function in Looker Studio for Text Modification and Data Presentation
Understanding the FORMAT_DATETIME Function in Looker Studio for Enhanced Data Analysis and Presentation
Looker Studio function : COUNT_DISTINCT