Calculated Fields: Figuring it out!

Calculated fields are a critical feature of Data Studio, and allow you to transform your data in a number of different ways. If you're at all familiar with functions and formulas in spreadsheets, you're more than halfway there to understanding calculated fields in Data Studio.

As of right now, Google Data Studio supports 82 different functions for calculated fields in seven different categories: 

• Aggregation (such as AVG to calculate an average)
• Arithmetic (such as ABS to return the absolute value of a number)
• Conditional (such as IF to return values based on conditional statements)
• Date (such as MONTH to return just the month of a date field)
• Geo (such as TOCITY to return a city name)
• Text (such as CONCAT to combine/concatenate text)
• Miscellaneous (such as HYPERLINK to create a clickable hyperlink in a table)

Calculated fields can be created either at the data source or the chart level in a Data Studio report. Data source calculated fields can be used over and over throughout any report that uses that data source, whereas chart-level calculated fields are created for use in a single graph, table, filter control or other element. more info

To create a data source calculated field, go to Resource > Manage added data sources, choose Edit for the data source to which you want to add the field, and then click + Add a Field in the upper right corner of the page.

To create a chart-specific calculated field in say, a selected table, look in the Data pane of the table's properties panel on the right, and click +Add dimension or +Add metric (depending on the kind of field you want to create.)

In both cases, you'll then need to give the field a name and then write a formula. Here are examples of calculated fields using two of my favorite functions:

• 
  Combining: Suppose your data source has fields for firstname and lastname, but you want to have a field that just gives a student's full name. You could use the CONCAT function to create a new field called Full Name. (Note that the example includes a comma and space between the two names.)
  

• 

  Transforming: Suppose you have a boolean field called IEP that is a 1 if a student has an IEP, and a 0 if they don't have an IEP, but you want the information to display more descriptively than just 0 or 1. You could use a CASE statement to create a new field called IEP Status.

        

• You could also use the IF function to do something similar.

For my spreadsheet friends: Please note that you don't need to type "=" before using any of these functions, like you do in Sheets or Excel (this took me some time to get used to!)

You might be asking, "If my data source is a Google Sheet, why wouldn't I just create these new fields as columns in my Sheet?" This is definitely another option, however, using calculated fields means you can do all the work on the Data Studio end without having to refresh the data source every time you add a new field, or "fill" Sheet formulas every time new rows are added to the spreadsheet.

Google has a simple tutorial to walk you through creating a few different types of calculated fields, if you want to try it out.