How to Use Calculated Fields in Looker Studio (e.g., Case When, Count If)

Calculated fields in Looker Studio allow you to create new metrics or dimensions from existing data by performing calculations or applying logic directly within your reports. This is especially useful when you need to derive new insights, such as segmenting data using conditional logic, calculating sums or averages, or even manipulating text. Two common examples of using calculated fields are CASE WHEN statements and COUNT IF operations.

Below is a step-by-step guide on how to create and use calculated fields in Looker Studio, including examples of CASE WHEN and COUNT IF formulas.

Step-by-Step Guide to Using Calculated Fields in Looker Studio #

1. Open Your Report and Access the Data Panel #

  • Open the report in Looker Studio where you want to use a calculated field.
  • Ensure that your data source is connected and available in the Data Panel on the right-hand side of the screen.

2. Add a New Field #

  • In the Data Panel, scroll down to the Available Fields section.
  • Click on the + Add a Field button at the bottom of the list to create a new calculated field.

3. Name the Calculated Field #

  • In the Create Field window, give your calculated field a descriptive name. For example, if you’re creating a field to categorize sales performance, you might name it "Sales Category."

4. Write the Formula for Your Calculated Field #

  • In the Formula box, you can now enter your custom formula based on your needs.

Example 1: Using CASE WHEN in Calculated Fields #

The CASE WHEN statement is a powerful conditional expression that lets you assign values to a field based on specific conditions. For instance, you might want to categorize sales performance into “High,” “Medium,” or “Low” based on a threshold.

Example: #

If you want to create a sales category based on sales numbers, you can write the following formula:

CASE
  WHEN Sales > 1000 THEN "High"
  WHEN Sales BETWEEN 500 AND 1000 THEN "Medium"
  ELSE "Low"
END
  • Explanation:
    • If the Sales field is greater than 1000, the new field will return "High."
    • If the Sales field is between 500 and 1000, it will return "Medium."
    • For all other values, it will return "Low."

Steps to Apply the Formula: #

  1. In the Formula box, enter the CASE WHEN formula.
  2. Click Validate to check for any syntax errors.
  3. If the formula is valid, click Save to add the field to your data source.
  4. You can now use this new calculated field in your report, such as by dragging it into a table or chart to categorize sales performance.

Example 2: Using COUNT IF in Calculated Fields #

While Looker Studio does not have a direct COUNTIF() function like Google Sheets, you can achieve similar results using a SUM() function combined with a CASE WHEN statement. This technique allows you to count occurrences based on a condition.

Example: #

To count how many times a particular condition is met (e.g., how many sales are greater than 1000), you can use this formula:

SUM(CASE WHEN Sales > 1000 THEN 1 ELSE 0 END)
  • Explanation:
    • For each row where the Sales value is greater than 1000, the formula assigns a value of 1.
    • For rows where the condition is not met, the formula assigns 0.
    • The SUM() function then adds up all the 1s, effectively counting how many rows meet the condition.

Steps to Apply the Formula: #

  1. In the Formula box, enter the SUM(CASE WHEN) formula.
  2. Click Validate to ensure there are no syntax errors.
  3. Click Save to add the field to your report.
  4. You can now use this calculated field to display the count of sales greater than 1000 in tables, scorecards, or other visualizations.

Additional Examples of Calculated Fields #

1. Simple Mathematical Calculations #

You can create new metrics by performing arithmetic on existing fields. For example, to calculate profit from revenue and cost:

Profit = Revenue - Cost

2. Percentage Calculations #

To calculate the conversion rate, for instance, you can divide the number of conversions by the number of sessions and multiply by 100:

Conversion Rate = (Conversions / Sessions) * 100

3. Text Concatenation #

You can concatenate text fields to create a full name or custom labels. For example:

Full Name = CONCAT(FirstName, " ", LastName)

4. Date Calculations #

You can perform calculations on dates to track time between events. For example, calculating the number of days since a purchase:

Days Since Purchase = DATE_DIFF(TODAY(), PurchaseDate)

Key Functions for Calculated Fields #

  • Mathematical Functions:

    • SUM(), AVG(), MIN(), MAX(), COUNT()

  • String Functions:

    • CONCAT(), LOWER(), UPPER(), TRIM()

  • Date Functions:

    • DATE_DIFF(), TODAY(), YEAR(), MONTH()

  • Logical Functions:

    • CASE, IF, AND(), OR()

Tips for Using Calculated Fields #

  1. Validate Regularly: Always validate your formulas after creating them to avoid syntax errors and ensure they work correctly in your report.
  2. Use Descriptive Names: Give your calculated fields clear, descriptive names so that they are easy to recognize and use in reports.
  3. Test Your Calculated Fields: After creating a calculated field, test it by applying it to different charts or tables to ensure that it works as expected.
  4. Optimize for Performance: Avoid overly complex formulas if possible, especially when working with large datasets, as they can impact report performance.

Conclusion #

Calculated fields in Looker Studio give you the ability to extend your data’s functionality by applying custom logic, performing calculations, and manipulating your existing data. Whether you’re using CASE WHEN for conditional logic or simulating COUNT IF for conditional counting, calculated fields provide flexibility and power in your reporting. By leveraging this feature, you can gain deeper insights and customize your reports to meet your specific analysis needs.

For more advanced calculations and customization, check out How to Add a Formula Field in Looker Studio or explore Creating Custom Formulas for more detailed guidance.

Published