Search
Clear search
Close search
Google apps
Main menu

Calculated fields

Add new dimensions and metrics derived from your data.

You can create new metrics and dimensions in your data source using calculated fields. Calculated fields let you manipulate the information contained in your data source. For example, you can apply mathematical formulas, extract or transform text, and return data based on logical comparisons. You can use these new field in your reports by selecting them from the metric or dimension picker, just as you would any other field.

In this article:

Watch a video

Use calculated fields

Create new calculated dimensions and metrics, which you can then use in your charts and controls

 

Create a calculated field

To create a calculated field:

  1. Edit your data source
  2. Click the blue plus at the top of the Field column:Add button
  3. Enter a name for this field.
  4. Enter the formula for this new calculated field. Example:Calculated field example
  5. Click CREATE FIELD on the right.
The formula bar offers you autocomplete help as soon as you start typing. Use it to create valid functions with minimal typing by selecting function, dimensions, and metrics from the list. Click a function in your formula to see help content for that function.

How calculated fields work

Calculated fields appear as new fields in your data source, and you can use them in charts and controls. You can also refer to a calculated field in another calculated field. Calculated fields work by applying a formula to each row of data displayed by the charts or controls using that field. Calculated fields are aggregated in the same way as regular fields.

Example

The following table lists some of the courses available in an online academy, along with the number of students enrolled.

Category Course Enrolled
Science Cold Fusion for Dummies

75

Science Catapults 101

15

Veterinary Dragon Agility Training

2

Music Build Your Own Bagpipes

108

Veterinary Teach Your Gerbil to Code

22

Veterinary Koala Racing: Rules and Regulations

5

Music Heavy Metal Ukulele

100

Science Refrigerator Microbiology

13

 

To summarize the courses and students enrolled per category, you can create a simple calculated field to count the number of courses using the COUNT function:

Number of Courses: COUNT(Course)

A table in Data Studio with this data might look like this:

Category Number of Courses Enrolled
Science

3

103

Veterinary

3

29

Music

2

208

 

In this example, Data Studio has aggregated (summarized) both the Enrollment field and Number of Courses calculated field per category.

Calculated field syntax

Formulas in calculated fields consist of the following:

Functions

Functions let you create formulas that use math, text handling, date transformation, logical comparison, and other operations. A formula can use multiple functions, along with operators, to create sophisticated calculated fields. Function names are case-insensitive.

See the function reference.

Arguments

A function argument tells the function what to act upon. Most functions expect one or more field-expressions as arguments. A field-expression is some text that evaluates to a field name in your data source. Typically, you will just use the actual name as it appears in the data source.

To refer to a field from your data source in a formula, just start typing its name, then select the field from the autocomplete list.

Some functions accept literal values, such as text or numbers. Be sure to enclose text in single or double quotes.

Still other functions expect arguments that specify date formats or geographic codes. Expand the sections below for more information on date and geo formats.

All date functions use UTC as their timezone.

Date formats

Supported date function input formats

Date functions can take an optional input-date-format string which formats the function output:

  • 'BASIC' - Supported format YYYY/MM/DD-HH:MM:SS
  • 'DEFAULT_DASH' - Supported format YYYY-MM-DD [HH:MM:SS[.uuuuuu]]
  • 'DEFAULT_SLASH' - Supported format YYYY/MM/DD [HH:MM:SS[.uuuuuu]]
  • 'DEFAULT_DECIMAL' - Supported format YYYYMMDD [HH:MM:SS[.uuuuuu]]
  • 'RFC_1123' - Sample format: Sat, 24 May 2008 20:09:47 GMT
  • 'RFC_3339' - Sample format: 2008-05-24T20:09:47Z
  • 'SECONDS' - Seconds since epoch
  • 'MILLIS' - Millis since epoch
  • 'MICROS' - Micros since epoch
  • 'NANOS' - Nanos since epoch
  • 'JULIAN_DATE' - Days since epoch
  • 'DECIMAL_DATE' - Same as 'DEFAULT_DECIMAL'
  • Any valid strptime format as string
Geo formats

In the Geo related functions, GeoInputFormat is one of the following strings:

  • 'CITY-ID'
  • 'SUB-CONTINENT-CODE'
  • 'COUNTRY-ISO-CODE'
  • 'REGION-ISO-CODE'

The valid geo codes are defined here:

GeoInputFormat is optional if the field expression is a derived column already containing semantic geo information, otherwise it is required.

Formula examples

CASE WHEN Favorite Color = "blue" THEN "Me, too!" ELSE "boo" END

MAX(Wait Time) + 1

CONCAT('Browser:', Browser, 'Version:', Version-number)

DAY(hit-time, 'MILLIS')

TOCITY(city-info, 'CRITERIA-ID')

Arithmetic operators

Numeric calculated fields support the following operators:

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

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

Operator examples

Price + Tax

(Students Enrolled / Students Passed) * 100

Return data type

All functions return results in a specific data type. Typically, this depends on the function itself: for example, math and aggregation functions return numbers, text functions return text. Most date functions return a number, except for the TODATE function, which returns text.

You can change or refine 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 will be summarized in your reports. Data Studio tries to assign the most appropriate aggregation to a calculated field. For certain functions, the aggregation is implicit: e.g., MEDIAN, AVG, SUM. In this case, the aggregation is set to Auto and cannot be changed. For other functions, you can specify the aggregation type using the Aggregation drop-down menu.

Data type and aggregation drop-down menus

Am I creating a dimension or metric?

The type of field you create—a new dimension or new metric—depends on the end result of your formula. If the result is a number of some kind, the new field will be a metric. If the end result is a string (text) or a date, the new field will be a dimension.

Edit calculated fields

After you've created a calculated field, click the fx chip to edit the formula.

Edit a calculated field

Troubleshooting calculated fields

If Data Studio finds an error with a calculated field, it will display a message and will not create the new field. Here are a few reasons why your formula might be rejected:

  • Trying to use an operator on a non-supported type.
  • Mismatched parentheses.
  • Incorrect number or type of arguments.
  • Attempting to aggregate an already aggregated field. For example, using the AVG() function on a field that is already summed or averaged.

Data Studio function list

Was this article helpful?
How can we improve it?
What's new in Data Studio

Learn about new features and recent changes.