Looker Studio1 (Google Data Studio) – Starting and Charts guide continuations – let’s take a look at how we can configure report content using calculated fields, and how we can create new metrics and dimension fields derived from our data2.
You can find support for Looker Studio here.
Calculated Fields (Calculated Fields)
In Looker Studio, two types of calculated fields can be created. One is a calculated field at the data source level (data source calculated field), and the other is a calculated field specific to a chart (chart-specific or chart level calculated field).
Usage of Calculated Fields
Calculated fields are areas that enable us to use metrics and dimensions obtained from data sources by expanding and/or transforming them within our reports. As previously mentioned, these fields can be adjusted based on both data source and chart level. Calculated fields, expressed as formulas, can include mathematical operations, text, date, and geographic data3. A calculated field created under a metric can handle numeric or monetary values, while a calculated field created under a dimension can process values such as numbers, text, and dates. Logical operations can also be included to enable evaluation and derive different results. The scope and appearance of a calculated field may vary depending on how it was created, and they can be used across different fields.
In the above visual example, I combined the “Main Machine Name” and “Page” information using the CONCAT() function as CONCAT(Main Machine Name, Page), and used this as a new dimension, as described in the article titled “WordPress MU Multi-Site Control and GA Domain Display”.
Calculated Fields at the Data Source Level (Data Source Calculated Fields)
I mentioned there are two types of calculated fields, each with its own characteristics and advantages. Let’s first examine calculated fields at the data source level. These types of calculated fields are linked to the relevant data source. This means they can be used in any report that utilizes the data source. Additionally, they can be used in charts, controls, and other calculated fields just like regular fields, and can also be filtered.
What are the limitations of using calculated fields at this level? First, access permissions must be granted to create calculated fields at the data source level. Additionally, we cannot use a calculated field created at the data source level with blended (combined) data.
Chart-Specific Calculated Fields (Chart-Specific or Chart Level Calculated Fields)
Calculated fields created specifically for a chart can be applied directly to a chart within a report. Mathematical operations, functions, and case statements can be used, and results based on these statements can be returned. One key advantage differentiating this field type from one created at the data source level is that we do not need access to the data source editor to create the field. Additionally, we can create calculated fields based on blended (combined) data.
Finally, we can also include calculated fields created at the data source level into the calculated fields we’ve created specifically for individual charts. However, a key limitation of this type of calculated field is that it exists only within the chart in which it was created and is solely based on the data retrieved for that specific chart from the associated data source.
Even if created within the same chart, these calculated fields cannot be referenced from calculated fields in other charts. To create a calculated field of this type, you must have editing permissions at the report level. Additionally, to create a calculated field based on a chart, the “Edit Field” option under the report’s fields must be enabled. To activate this setting, follow the steps: File > Report Settings > Data Source > Edit Fields in Reports and select Active for the relevant option. This activation will be displayed on the field creation window at the data source level.
Comparison of Calculated Field Types
If we summarize the differences in usage and features discussed above under separate headings in a table:
| Feature | Calculated Field at Data Source Level | Calculated Field at Chart Level |
|---|---|---|
| Who can create it? | Authorized users at data source editing level | Authorized users at report editing level |
| Can it operate on combined data? | No | Yes |
| Can it include other calculated fields? | Yes | No |
| What is its scope of use? | All reports using the same data source | Only within the chart it is associated with |
Calculated Fields and Data Types
Calculated fields within Data Studio also possess specific semantic (meaningful) data types, just like regular fields. For example, when a calculated field uses arithmetic or aggregation functions, the data type of the calculated field will be Number. If a text function is used, however, the data type will be Text. Most date functions, except for TODATE, return a Number type. As shown in the example visuals, the data type of a calculated field can be edited in the section where the calculated field definition is created.
Calculated Fields and Operations
When discussing that calculated fields have a specific data type, we can also discuss what operations we can perform with them. If the field’s data type is Number, we can perform simple arithmetic operations using operators (+ addition, - subtraction, * multiplication, and / division). We can also define the order of operations using parentheses ().
For example, to calculate the ratio of new users to total users, we can create a calculated field with the following expression: Users / New Users.
In addition to arithmetic operations, calculated fields enable data evaluation in various ways through the use of functions—such as mathematical and statistical operations, text manipulation, and date and geographic content. I will provide a detailed explanation of these functions in a separate post.
Finally, we can use CASE statements to perform logical if/then/else operations and return results. For a detailed explanation of this usage, a separate post would be more appropriate.
In summary; we can create and use reports or relevant charts as needed, by leveraging metrics and dimensions obtained from the data source and calculated fields. Although there may be differences in usage patterns and functionalities, these types of operations are already familiar features in many data analysis and visualization applications. Therefore, I’ve tried to keep the explanation section as concise as possible. The detailed section I’ll focus on will be the functions and potential issues that may arise. Alongside the written content, I aim to further solidify the topic through videos and sample reports.