Tutorial: Create a ratio field based on filtered metrics
Stay organized with collections
Save and categorize content based on your preferences.
A common use case in reporting and data visualization is to display key performance indicators (KPI) as ratios of one indicator as compared to another. For example, suppose you are measuring the completion rate of an online coding academy. A KPI for the course is completion rate, which we'll define here as:
Course completions / Course registrations
If your dataset already contains this data then you can display the result in Looker Studio. But what if you need to calculate it in your report, or need to display completion rates based on filtered data? The solution is to use data blending.
Say your data looks like this:
Event Category
Event Action
Course Number
Unique Events
Course Certificate
Download
9
299
Course Complete
Fail
8
21
Course Complete
Fail
9
86
Course Complete
Pass
9
337
Course Complete
Fail
6
34
Course Complete
Pass
7
363
Course Complete
Fail
7
17
Registration
Register
8
814
Registration
Register
7
475
Registration
Register
5
574
Registration
Register
9
1078
Registration
Unregister
6
22
Step 1: Calculate the course completions
Add a scorecard to your report. Call it Course Completions.
Set the metric to Unique Events.
Add a filter to the scorecard to ignore non-completion events:
IncludeEvent Category= "Course Complete"
AND
IncludeEvent Action= "Pass"
Based on the sample data, the scorecard would look like this:
Course Completions
700
Step 2: Calculate the course registrations
Add a scorecard to your report. Call it Course Registrations.
Set the metric to Unique Events.
Add a filter to the scorecard to ignore non-registration events:
IncludeEvent Category= "Registration"
AND
IncludeEvent Action= "Register"
Based on the sample data, the scorecard would look like this:
Course Registrations
2,941
Step 3: Blend the data from the scorecards
Select the Course Registrations scorecard, then select the Course Completions scorecard.
Right-click, then select Blend data.
This creates a new scorecard that automatically calculates the ratio between those metrics:
[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-28 UTC."],[],[],null,["# Tutorial: Create a ratio field based on filtered metrics\n\nA common use case in reporting and data visualization is to display key performance indicators (KPI) as ratios of one indicator as compared to another. For example, suppose you are measuring the completion rate of an online coding academy. A KPI for the course is completion rate, which we'll define here as:\n\n`Course completions / Course registrations`\n\nIf your dataset already contains this data then you can display the result in Looker Studio. But what if you need to calculate it in your report, or need to display completion rates based on filtered data? The solution is to use data blending.\n\nSay your data looks like this:\n\nStep 1: Calculate the course completions\n----------------------------------------\n\n1. Add a scorecard to your report. Call it **Course Completions**.\n2. Set the metric to *Unique Events*.\n3. Add a filter to the scorecard to ignore non-completion events:\n\n `Include`**`Event Category`** `= \"Course Complete\"`\n\n `AND`\n\n `Include`**`Event Action`** `= \"Pass\"`\n\nBased on the sample data, the scorecard would look like this:\n\nStep 2: Calculate the course registrations\n------------------------------------------\n\n1. Add a scorecard to your report. Call it **Course Registrations**.\n2. Set the metric to *Unique Events*.\n3. Add a filter to the scorecard to ignore non-registration events:\n\n `Include`**`Event Category`** `= \"Registration\"`\n\n `AND`\n\n `Include`**`Event Action`** `= \"Register\"`\n\nBased on the sample data, the scorecard would look like this:\n\nStep 3: Blend the data from the scorecards\n------------------------------------------\n\n1. Select the *Course Registrations* scorecard, then select the *Course Completions* scorecard.\n2. Right-click, then select **Blend data**.\n\nThis creates a new scorecard that automatically calculates the ratio between those metrics:\n\n| **Tip:**\n|\n| To create more complex calculated fields or change the number display:\n|\n| 1. Select the blended scorecard.\n| 2. Edit the metric.\n| 3. Enter a new formula and/or change the field **Type** to match the data.\n|\nRelated resources\n-----------------\n\nLearn more about how blending works:\n\n- [About data blending](/looker/docs/studio/how-blends-work-in-looker-studio)\n- [Use blending to re-aggregate data](/looker/docs/studio/use-blending-to-reaggregate-data)"]]
