About calculated fields

Transform, categorize, and do math with your data by adding new fields to your data source.

Calculated fields let you create new metrics and dimensions. Once you've created a calculated field, you can use it in charts, controls, and other calculated fields, just like a regular field.

You must have edit rights to the data source to create or edit calculated fields.
In this article:

Watch a video

This video explains the basics of creating calculated fields, including using simple math, CASE statements, and the REGEXP_MATCH function.

This video shows an earlier version of the calculated field user interface. We'll be updating this shortly.
Use calculated fields

How calculated fields work

A calculated field is a formula that performs some action on one or more other fields in your data source. Calculated fields can perform arithmetic and math, manipulate text, date, and geographic information, and use branching or boolean (true/false) comparisons to evaluate your data. The result of that formula can then be displayed for every row of data in charts and controls that include the calculated field. How this new data is displayed depends on how it's used.

For example, say you create a calculated field called Total that multiplies a quantity sold field (Qty Sold) by a unit price field (Price):

When used in a table, the calculated Total field shows the product of that multiplication for each row.

Example table showing total price calculated field.

When used in scorecard, the Total field displays the sum of the products for all rows in which quantity is multiplied by price.

You can filter on calculated fields in the same way as normal fields. For example, you could set a filter property to include only items with a combined value of $500 or more:

Data type

Just like regular fields, calculated fields have a specific semantic data type. For example, if your calculated field uses arithmetic or aggregation functions, the calculated field's data type is Number. If the calculated field uses a text function, the data type is Text.

Most date functions return a Number, except for the TODATE function, which returns a Date type.

You can change the data type of your calculated fields using the Type drop-down menu in the data source editor.

Aggregation

A field's aggregation determines how it can be used and summarized in your reports. If the result of a calculated field's formula is an aggregated number of some kind, the new field is a metric. If the end result is a string (text), a date, or an unaggregated number, the new field is a dimension.

Data Studio tries to assign the most appropriate aggregation to a calculated field. For most arithmetic and aggregation functions, the field aggregation is Auto.

You can't change the Auto aggregation type.

For other functions, you can specify the aggregation type using the Aggregation drop-down menu.

Data type and aggregation drop-down menus

What you can do with calculated fields

Calculated fields let you do the following kinds of things.

Do basic math with numeric fields

You can do simple arithmetic calculations using the normal operators:

  • Addition: +
  • Subtraction: -
  • Division: /
  • Multiplication: *

You can construct a calculated field using any of the operators above in combination with static numeric values and unaggregated numeric fields from your data source. Use parentheses to force calculation order.

Examples

Users / New Users

(Price * Quantity) * .085

Manipulate your data with functions

Functions let you aggregate your data in different ways, apply mathematical and statistical operations, manipulate text, and work with date and geographic information.

Examples

SUM(Quantity) - adds the values in the Quantity field.

PERCENTILE(Users per day, 50) -- returns the 50th percentile of all values of the Users per day field.

ROUND(Revenue Per User, 0) -- rounds the Revenue per User field to 0 places.

SUBSTR(Campaign, 1, 5) -- returns the first 5 characters of the Campaign field.

REGEXP_EXTRACT(Pipe delimited values, '^([a-zA-Z_]*)(\\|)') -- extracts the first value in a pipe delimited string.

Note the use of the double backslash to escape the | character.

DATE_DIFF(Start Date, End Date) -- calculates the number of days between Start Date and End Date.

TODATE(concat(Year, '-', Month Number, '-', Day Number), "DEFAULT_DASH", "%Y%m%d") -- create a date from concatenating separate fields containing valid date parts.

TOCITY(Criteria ID, "CRITERIA_ID") -- Display the associated city name from a valid Google Ads Geographical Targeting criteria ID.

Learn more about using functions.

Use branching logic in calculated fields

CASE statements let you perform branching "if/then/else" style logic in your calculated fields. For example, the following CASE formula categorizes the specified countries into regions, while grouping unspecified ones into an "Other" category:

CASE 
    WHEN Country IN ("USA","Canada","Mexico") THEN "North America" 
    WHEN Country IN ("England","France") THEN "Europe" 
    ELSE "Other" 
END

Learn more about CASE.

Was this article helpful?
How can we improve it?