How to build calculated fields in Looker Studio and what are the most useful formulas?

Sometimes, your dataset does not give you exactly what you want. There might be an issue with a dimension, or a missing calculation on metrics. Reason why you need to build calculated fields in Looker Studio (formerly known as Data Studio).

There are 3 ways to overcome this issue:

  • Change the dataset (outside Looker Studio here) so when you create your data source, you've got exactly what you want. Best option, but sometimes not possible if you don't own the connector or don't know how to write custom queries in Big Query for instance
  • Create a new field in the data source, so it can be re-used in all data visualizations sharing the connector (my favorite option)
  • Create a new field directly in your data visualization. Very similar to the previous option, a bit faster (as you can test real-time), but then you can't re-use the newly created field elsewhere nor use it to filter across data sources

In this article, we'll review options 2 & 3 (which in reality are similar), as well as a list of the most useful formulas you'll need to know to build powerful calculated fields in Looker Studio. Hope it helps :)

Need help ? We offer Looker Studio Support for companies.

Looking for a connector ? Check Supermetrics.

What are calculated fields?

Quite easy to understand, a calculated field is just a new field based on a formula. It can be as simple as something alongs the line of if channel IN X, Y, Z then on-line else off-line (but in Looker Studio, we use CASE WHEN!), or a numeric calculation like sessions * 2.5 - 10.

The newly created calculated field can then be used as a dimension or a metric in your data visualizations. But please remember that you need to be careful with aggregates: if sessions > 500 then ... does not always give the same result as if SUM(sessions) > 500 then ... .

Build a calculated field in a data source

My favorite option. Please go to the top menu, and click Resource, Manage added data sources, and then Edit the one where you want to add a field.

Looker studio calculated fields add a field

Top right of the box, you should see a Add a field button, and this is where the magic will happen. Basically, where you will add your formula and set up a few parameters:

Looker studio calculated fields write formula

First thing first, please give a name to your field in Field Name (I love it when I write transcendent comments like this). Then, let's talk a second about the Field ID. This ID is used by Looker Studio to understand that a field is shared across data sources.

What does it mean? If you have a country field in a GA4 data source, and another country field in a Search Console data source with a different ID, creating a control on one won't filter anything on the dataviz based on the other. Because for Looker Studio, they are different fields. How to overcome this issue? Please refer to this article I wrote about How to build a control that filters across several data sources in Looker Studio?

For the moment, keep it as is, but in the near future I'll bet you want to use meaningful IDs to share them across data sources and build nice controls. Plus, once the field is created, you can't modify the ID, so think first!

Then, we're getting to the formula. No need to start with an equal sign, just drop your formula. Remember that you can't mismatch dimensions & metrics, so if your formula is based on a dimension, then only dimensions are accepted. If based on a metric, then only metrics are accepted.

Your formula can either be numeric (see below), or based on dimensions using LIKE REGEX... Please refer to this article on How to build a field using a CASE WHEN statement in Looker Studio? to learn more.

Looker studio calculated fields your first formula

If you have an error in your formula, you'll see an exclamation point at the bottom of your screen with some explanations of what the issue is. If it's fine, you'll see a green check, you're good to go.

Looker studio calculated fields formula error

Build a calculated field in a data visualization directly

The other option I mentioned is to build your calculated field directly in the data visualization. For, it's really fast and easy. Against, you can't re-use the field in other data visualization or filter across data sources. But most of the time, it's good enough for single-time use fields.

To build one, open the setup ribbon of your dataviz, and click Add dimension or Add metric, Create field. When the pop-up opens, give your field a name and insert your formula. You'll then be able to change its Type, as usual:

Looker studio calculated fields formula in data visualization

As mentioned, be careful with aggregates as they can be misleading. My recommendation is to add aggregators in formulas to be sure that's you're doing what you think you're doing. So, instead of: Revenue - Tax. Write: SUM(Revenue) - SUM(Tax) or SUM(Revenue - Tax).

Useful formulas that you'll face in your Looker Studio journey

This is a very quick list of what you'll be able to build using calculated fields. There is a lots of documentation out there, but to give you some ideas:

  • CASE WHEN: the IF THEN of Excel, based on SQL langage. I wrote an article about this
  • CAST: change type of your field
  • CONCAT: to transform your text fields
  • CONTAINS_TEXT: to check if a text contains certain characters
  • CURRENT_DATE: returns today's date
  • DATETIME_ADD / DATETIME_SUB: to make date calculation
  • HYPERLINK: to add links directly in your table, I also wrote an article about that
  • IFNULL: prevent from showing null. If your dimension is null, you can return something else. Don't mistake with NULLIF
  • MAX / MIN / MEDIAN: self-explanatory I guess
  • REGEX: quite complicated, but you can then do pretty much anything
  • YEAR / MONTH / WEEK / DAY: to extract a part of a date

And that's it! In this article, you've learned how to build calculated fields, so your dataset is not a limitation anymore.


Previous article: How to blend data in Looker Studio - part 1?

Next article: How to do a year to date, year over year comparison in Looker Studio?

Looking for something else? All our articles can be found on our blog page