About calculated fields
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.
Watch a video
This video explains the basics of creating calculated fields, including using simple math, CASE statements, and the REGEXP_MATCH function.
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.
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:
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.
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
For other functions, you can specify the aggregation type using the Aggregation drop-down menu.
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.
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.
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.
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.
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