Data Studio provides a number of powerful functions that can be used inside of calculated field formulas. In this article, we are going to explain how to use calculated fields in Google Data Studio. To create an aggregated calculated metric, include the desired aggregation functions for any of the numeric fields that make up the formula. Addition (+), Subtraction (-), Division (/) and Multiplication (*). Y is a whole number specifying the … You can use a data source calculated field in charts, controls, and other calculated fields, just like a regular field. The operators (and formulas) used in GDS are the same as Microsoft Excel or Google Sheets: We are going to create a new metric called ‘Tax paid by customer’ using the data from our example data source. TOCITY(Criteria ID, "CRITERIA_ID") -- Display the associated city name from a valid Google Ads Geographical Targeting criteria ID. To create a new metric select the ‘+ Add Field’ on the right-hand side of the screen. For example, say you create a calculated field called Total that multiplies a unit price field (Price) by a quantity sold field (Qty Sold): When used in a table, the calculated Total field shows the product of that multiplication for each row. In this article, we are going to explain the process of creating a new metric in 10 steps. To begin, it is necessary to export the spreadsheet into GDS. Once you have created a new calculated metric, you have the ability to use it when creating additional ones. To start, enter a name for your metric. For example, suppose you want to display your total profit margin in a scorecard. In the GDS home screen you will see a list of your metrics in blue, and your dimensions in green. By using ‘calculated fields’, it is possible to create new, custom metrics which derive from your data source. Formulas that use date and time functions create Number, Date or Date & Time types, depending on the function used. how to connect Google Sheets to Google Data Studio, How to Build a Custom Data Studio Dashboard, Why your agency should switch to Google Data Studio. Using this in a chart uses the default aggregation of Sum and calculates the total per row of your data. Addition (+), Subtraction (-), Division (/) and Multiplication (*). //www.google.com/tools/feedback/metric/report, Data source vs. chart-specific calculated fields, Only the specific chart in which it was created. You can include data source calculated fields in chart-specific calculated fields. You can filter on a data source calculated field, just like a regular field. Your email address will not be published. View report. This help will appear as you begin to type your formula. Formulas that use text functions, such as CONCAT, SUBSTR, or LOWER, create Text. If you haven’t experienced the peace of mind that comes from a custom-built dashboard that was designed just for you, Google Sheets and Data Studio may seem like rivals at first glance, but the truth is that both excel at, As a digital agency, you’re going to be dealing with a lot of different data sources in your day to, How to Use Calculated Fields in Google Data Studio, 7) Calculating your metric using formulas. The next step is to input the formula for your new metric in the formula field: When entering a new formula, GDS generates suggested metrics. To be able to create chart-specific calculated fields, you must be an editor of the report. Calculated fields are essentially user-generated metrics. Chart-specific calculated fields offer some advantages over data source calculated fields: The following table summarizes the differences between these 2 kinds of calculated fields. A metric is a measure used to quantitatively track or assess progress, performance, quality and more. How to subtract microseconds from a date field in Data Studio? DATETIME_DIFF(Start Date, End Date) -- calculates the number of days between Start Date and End Date. The operators (and formulas) used in GDS are the same as Microsoft Excel or Google Sheets: 3) Creating a new Metric. The output of a calculated field can then be displayed for every row of data in charts that include that field. The data comes from two sources, Google Sheets and Google Analytics . Some examples from Google Analytics could be Events per user which is total Events / Users or Product View per Session which is Product Detail Views / Sessions. Google Data Studio SIN() Function Examples, Google Data Studio POWER() Function Examples, Google Data Studio TOSUBCONTINENT() Function Examples, Google Data Studio SECOND() Function Examples, Google Data Studio MEDIAN() Function Examples. Your new metric will then be available to add to your data visualisation. ROUND(Revenue Per User, 0) -- rounds the Revenue per User field to 0 places. (If you need to reference other calculated fields, use a data source calculated field.). For this example, we are looking at two metrics, tax rates and product prices: We want to be able to calculate the amount of tax that an individual customer would pay when they purchase a specific product. How to add microseconds to a date field in Data Studio? You can construct calculated fields that work on unaggregated, row-by-row values, or on aggregated values. There are 2 kinds of calculated fields, determined by where you create them: in the data source, or in specific charts in a report. A calculated field is a formula that performs some action on one or more other fields in your data source. ROUND(X, Y) where X is a field or expression containing at least one field. Required fields are marked *. You can revisit our previous article on how to connect Google Sheets to Google Data Studio. Once complete, select ‘done’. How this new data is displayed depends on how it's used. For this example we are using ‘Tax paid by customer’. Google Data Studio ROUND (X, Y) function gets all values from X and returns the rounded number to a certain number of decimal places as stated in Y. How to add milliseconds to a date field in Data Studio? Data Studio ROUND Function Example. This report illustrates various styles of filter controls. We are going to create a new metric called ‘Tax paid by customer’ using the data from our example data source. This is a great way to complement your dashboard. Next, select the data type for this new field. How to subtract milliseconds from a date field in Data Studio? The calculated field is available in any report that uses that data source. Welcome to Data Studio! Google Data Studio ROUND(X, Y) function gets all values from X and returns the rounded number to a certain number of decimal places as stated in Y. Calculated fields can perform arithmetic and math, manipulate text, date, and geographic information, and use branching logic to evaluate your data and return different results. Each kind of calculated field offers certain advantages over the other. Your new metric will then display in your list highlighted in blue, with a small icon ‘fx’ indicating that it’s a calculating field. How to subtract seconds from a date field in Data Studio. CASE statements let you perform branching "if/then/else" style logic in your calculated fields. Formulas that use arithmetic or aggregation functions, such as SUM, COUNT, or MAX, create Numbers. Functions let you aggregate your data in different ways, apply mathematical and statistical operations, manipulate text, and work with date and geographic information.