Tutorial: create calculated fields

Goals

• Learn how to create a simple arithmetic calculated field.
• Learn how to break more complicated calculations into simpler steps.
• Create a conditional expression to evaluate your data and return different results.

Scenario

Suppose you manage an online pet supply store. You might measure sales data, such product codes (SKU), item names, price, and quantity sold. Here's a simple example:

Calculated fields in Data Studio can help you manipulate and extend your data. In this exercise, you'll use calculated fields to:

• calculate the total value of each order
• apply a discount to orders over a certain amount
• categorize sales into departments.

Sample data

To complete this exercise as written, use the sample data below. Otherwise, use your own data (adjusting the field names accordingly). You'll need access to Google Sheets. Alternatively, you could use the File Upload connector to import the data into Data Studio.

Instructions

Prepare the data

1. Copy the sample data in the box above.
2. Open a text editor, paste in the sample data, then save the file as pet-store-data.csv.

   • Be sure to save this file as plain text, otherwise Sheets won't be able to import the data.

     • On Windows, you can use Notepad.

     • On Mac OS, you can use TextEdit, then select Format > Make plain text.

     • On Chrome OS, you can use the Text app, or Docs and then select File > Download and choose Plain Text.

• Create a new Google Sheet.
• In Sheets, select FileImport.
• Select Upload, then upload the sample data file you created in step 2.
• Select Replace spreadsheet.
• Name your spreadsheet Pet Store.

Create a report and add the data

1. Create a new report in Data Studio.
2. In the Add data to report panel, select the Google Sheets connector.
3. Select the Pet Store spreadsheet and worksheet you just created.
4. Leave the default options selected.
5. In the lower right, click ADD.

In a moment, you'll see the new report, with a table on the canvas that uses fields from the Pet Store data source.

Create the subtotal field

1. In the menu, select Resource > Manage added data sources.
2. To the right of the Pet Store data source, click EDIT.
   The data source fields panel appears.
3. On the top right, click ADD A FIELD.
   The calculated field editor appears.
4. In Field Name, enter Subtotal.
5. In Formula, enter Price * Qty Sold.
   The formula editor autocompletes dimension, metric, or function names as you type. You can also drag fields from the Available Fields list on the left into your formula.
6. On the bottom right, click SAVE.
7. On the left, return to the main data source editor by clicking ALL FIELDS.

Create the discount field

Let's say you want to give different discount rates depending on the value of the order. Create another calculated field, called Discount, that evaluates the Subtotal field and applies the following discounts.

Hints:

• In this step, you'll only calculate the discount. You'll apply it in the following step by multiplying the Subtotal field by the Discount field.
• Use the CASE statement to evaluate the Subtotal field.
  • Use a WHEN clause for each discount rate.
  • Use an ELSE clause to return the full price (0% discount).
• Express the discount as a decimal value. For example, a 25% discount would be .25.

Create another calculated field, called Total, that multiples the Subtotal field by the Discount field.

Solution

Subtotal * Discount

Note that you could also have accomplished the same thing by including the subtotal calculation directly:

(Price * Qty Sold) * Discount

But because of a limitation in CASE statements, you can't combine the discount evaluation and the total calculation in one field. For example, the following WILL NOT WORK:

Error: THEN/ELSE statements cannot contain functions or mathematical operations.

Understanding how and when you can accomplish your goal in a single calculated field, and when you'll need to break the calculation into separate steps, will make you more successful with calculated fields.

Create the department field

Create a final calculated field, called Department, that parses the SKU field and assigns new values depending on the SKU. You'd like to group dog, cat, and bird related items into separate departments.

See your data in a report

1. After saving the Department field, on the left, return to the data source editor by clicking ALL FIELDS.
2. In the upper right, click DONE.
3. On the right close the manage data sources panel.

You can now add your new calculated fields to charts:

4. Select the table.
5. On the right, drag each calculated field you created from the Available Fields list to the dimension and metrics section of the Data panel, as shown below:

Related resources

• About calculated fields
• Function list