Overview

Looker provides the ability for non-developer users to create and utilize ad hoc fields for richer data analysis. This is done by creating custom measures, custom dimensions, table calculations and using custom groupings to narrow down data to match specific conditions.

Using ad hoc fields gives non-developers the ability to create new fields, as opposed to regular fields, which require that you have development permissions and understand LookML allowing them to have more flexibility in finding data they are looking for independently.

Objectives

In this lab, you will learn how to:

• Create custom measures based on an existing dimension

• Create custom groupings

• Adding filters to custom measures

• Use table calculations on numeric fields without writing Looker functions or operators

Task 1. Create a custom measure

In this section, you will create a custom measure based on an existing dimension.

Using the dimension's three-dot Options menu

1. First, on the bottom left of the Looker User Interface, click the toggle button to enter Development mode.

2. Click the Explore tab and then select E-Commerce Training > Order Items.

3. Expand the Inventory Items section.

4. Click More () next to the Cost dimension to review the custom fields you can add.

5. Select the Aggregate > Average custom field to create a measure that calculates the average of an order item’s cost.

The suggested functions vary based on the type of dimension you’ve chosen (such as number, text, and date). If you select count, a count distinct is executed in the generated SQL. Your new field is automatically added to the query.

6. Expand the Custom Fields section to see your new field.

7. As with other fields, you can click a custom field’s name to add or remove it from a query. You can also hover over the field to reveal more options available for that field, including clicking its Filter by field icon to use it as a filter in a query.

Using the Custom Fields section

If you want to define a format or a filter for your custom measure while creating it, start with the + Add button on the Custom Fields section.

1. Expand the Custom Fields section.

2. Click + Add and then select Custom Measure.

3. Set the Field to measure to Inventory Items > Cost.

4. Set the Measure type to Average.

5. Set the Name to Average of Cost.

6. On the Field details tab set the following:

• Format: U.S. Dollars

• Decimals: 2

7. Click Save.

Task 2. Create a custom grouping

The Group custom field type lets you create ad hoc custom groups for dimensions and custom dimensions without needing to use logical functions in Looker expressions or needing to develop CASE WHEN logic in sql parameters or type: case fields.

This can be helpful when you want to assign fixed labels or category names to values that match specific conditions, for example, by grouping specific states or countries into regions or order costs into categories.

1. You should now see two Average of Cost custom fields in the Custom Fields section. Select just the bottom one to add it to the query.

2. Expand the Inventory Items section and select the Product Name dimension to add it to the query.

3. Expand the Users section and select the Filter by field icon for the Country dimension and set the drop down to is equal to and the value to USA.

4. Next, click More () next to State under the Users section and select Group.

5. In the Group By State dialog, set the Add group name to Pacific Northwest.

6. Click the is any value input box and add Oregon, Idaho and Washington to the list independently.

7. Check the Group remaining values checkbox then click Save.

8. Select the newly create State Groups custom field to add it to the Explore and click Run in the top right of the page.

The result will be a view of the average cost of various product names grouped by Pacific Northwest and Other.

Task 3. Adding a filter to a custom measure

Now that you have a view of the average cost of various product names grouped by Pacific Northwest and Other, say you wanted to only view products with an average cost greater than $200 (USD).

To enhance the current view do the following:

1. Under the Custom Fields section select the Filter by field icon for the bottom Average of Cost under Measures.

2. Set the drop down to is greater than and set its value to 200.

3. Re-run the query by clicking on the Run button in the top right of the page to view the results.

You will now see a filtered view with only the average of cost products greater than $200 (USD).

Click Check my progress to verify the objective.

Create a custom measure

Check my progress

Task 4. Using table calculations

Say you wanted to understand the count of orders for each item in the view from the last section. To filter the view further you could add the Order Count and use the Percentage of Column quick calculation to get an idea of how popular a product is by the State Groups custom field created earlier in this lab.

1. Under Order Items section, click Order Count to add it to the view.

2. In the data view, click Settings () on the Order Count column.

3. Click Calculations > % of column.

A new table calculation column with the percentage of Order Count should be populated into the Explore.

4. Click Run at the top right hand side of the page. This will re-run the previous query and the result will display the order item count and percentage of the table that each row represents in the resulting view. This will give an analyst insights into what product is popular by the State Grouping group created earlier.

Click Check my progress to verify the objective.

---

Solution of Lab

🚀 Task 1: Create a custom measure

1. Toggle on Development Mode (find it at the bottom-left corner).

2. Navigate to Explore > E-Commerce Training > Order Items.

3. Expand the Inventory Items section.

4. Click the More options (⋮) icon next to the Cost field.

5. Choose Aggregate > Average.

6. Expand Custom Fields to see your newly created measure.

7. Alternatively, you can create it manually:

• In Custom Fields, click + Add > Custom Measure.

• Field to measure: Inventory Items > Cost

• Measure type: Average

• Name: Average of Cost

• Format: U.S. Dollars

• Decimals: 2

• Click Save.

---

📊 Task 2: Create a custom grouping

1. In the Custom Fields section, select your Average of Cost measure (the one at the bottom if there are duplicates) to add it to your query.

2. Add Product Name (from Inventory Items) to the query.

3. Add Country (from Users), then click the filter icon:

• Set the filter to is equal to > USA.

4. Click the More options (⋮) icon next to State (from Users) and select Group.

5. Configure the Group By State settings:

• Group name: Pacific Northwest

• Add these values: Oregon, Idaho, Washington.

• Ensure Group remaining values is checked.

• Click Save.

6. Select the newly created State Groups field to add it to your view.

7. Click Run.

---

🔍 Task 3: Adding a filter to a custom measure

1. In Custom Fields, find your Average of Cost measure (again, the bottom one if duplicated) and click its Filter by field icon.

2. Set the filter condition to is greater than > 200.

3. Click Run.

---

🧮 Task 4: Using table calculations

1. Add Order Count (from Order Items) to your query.

2. Click the Settings (⚙️) icon on the Order Count column in your results table.

3. Select Calculations > % of column.

4. Click Run.