How to Calculate Average in Looker

Calculating an average in Looker is a fundamental skill for any analyst, but it can be surprisingly tricky depending on what you're trying to measure. This guide breaks down the different ways to correctly calculate averages in Looker, moving from the simple built-in options to more powerful custom logic for complex business questions.

Choose Your Method: An Overview of Calculating Averages

Unlike a spreadsheet where you might just use a simple AVERAGE() formula, Looker's power comes from its ability to understand the structure of your data. This also means you need to be precise about what kind of average you need. There are three primary ways to do this, each suited for different situations:

• LookML Measures: The most robust and reusable method. Ideal for frequently used averages like "Average Order Value" or "Average Session Duration" that you want everyone on your team to see and use consistently.
• Table Calculations: The quickest method for "on-the-fly" calculations. Perfect for when you have a set of results in an Explore and you want to find the average of one of the columns without modifying your model.
• Custom Fields: A middle ground that offers more flexibility than table calculations without requiring a developer to write LookML. Great for analysts who need to create custom aggregates for a specific report.

Method 1: Using Built-in LookML Measures for Averages

The best way to calculate a standard average is by defining it directly in your LookML model. This makes the calculation a permanent, reusable field that anyone in your organization can drag into their Explore reports. It ensures consistency, as everyone is using the exact same definition for the metric.

A LookML "measure" is an aggregate field - it performs calculations (like sums, counts, or averages) across multiple rows of data.

Step-by-Step: Creating a Simple Average Measure

Let's say you have a table of order_items and you want to calculate the average sale price of each item. This is a perfect use case for a standard {type: average} measure.

1. Locate Your View File

In your LookML project, navigate to the view file that corresponds to the data you want to average. In this case, it would be a file named something like order_items.view.lkml.

2. Define the Measure

Within the view file, add the following LookML code. A good practice is to group all your measures together after your dimensions.

measure: average_item_sale_price {
  type: average
  sql: ${sale_price} ,,
  description: "The average price of items sold"
  value_format_name: usd
}

3. Understand the LookML Parameters

• measure: average_item_sale_price: This declares a new measure and gives it a machine-readable name.
• type: average: This is the key component. It tells Looker to generate the appropriate SQL AVG() function.
• sql: ${sale_price}: This points to the dimension with the column of numbers you want to average. The ${...} syntax is how Looker references other fields.
• description: Although optional, adding a description is a great way to tell business users exactly what this field represents. It shows up when they hover over the field name in an Explore.
• value_format_name: usd: This applies formatting to your output, in this case, a standard US dollar format (e.g., "$123.45"). You can use other presets like gbp, eur, or custom formats.

4. Save and Test in an Explore

After saving your LookML changes (and pushing them to production if your project is configured that way), you can go to an Explore and test your new measure. Simply find "Average Item Sale Price" in the field picker and add it to your query alongside any relevant dimensions like "Product Category" or "Created Date".

Method 2: Using Table Calculations for Quick Analysis

What if you just want a quick average and don’t need a permanent new field? Table Calculations are your best friend here. They perform calculations on the data returned in your Explore table, similar to writing a formula in an Excel column.

A common scenario for this is averaging a pre-aggregated number. For instance, imagine your report shows "Number of Users" grouped by "Country". You can't use a {type: average} measure for this, because that averages all the raw user IDs. Instead, you want the average of the "Number of Users" column in your results.

Step-by-Step: Calculating an Average with a Table Calculation

Let's create a report showing the average number of users across different countries.

1. Build a Query in an Explore

Start by creating a simple query. Select "Country" as a dimension and "Count of Users" as a measure. Run the query. Your table will look something like this:

2. Open the Table Calculation Editor

Click the "Add new calculation" button in the Data bar. This will open the editor.

3. Write the Average Formula

Looker has a specific function for this: mean(). The mean() function calculates the average of all values in a single column.

mean(${users.count})

In this formula, ${users.count} references the "Count of Users" measure in our table. Looker's editor will help you autocomplete the field names as you type. Name your calculation something like "Average Users per Country" and choose a format before hitting "Save".

Looker will instantly add a new column to your table with the average calculated from the "Count of Users" column. This does not change your LookML and will only exist for this saved Look or report.

Method 3: Advanced Averages for Business Logic

Sometimes, a simple average of one column isn't what you need. A more common business metric is a ratio of two aggregated numbers. The prime example is Average Order Value (AOV), which is not AVG(order_total) but rather SUM(total_revenue) / COUNT(total_orders).

Wrongly calculating this is one of the most common issues users face. Averaging the order total on an order_items table is inaccurate if a single order has multiple items, this method would give you the average item price, not the average order value.

To solve this, you need to divide one measure by another. Here’s how to build it correctly in LookML.

Step-by-Step: Building an 'Average Order Value' Measure

1. Define Your Base Measures

First, make sure you have measures for the numerator and the denominator. You need a way to sum all the revenue and a way to uniquely count the number of orders.

In your order_items.view.lkml (or similar) file, define these two measures:

measure: total_revenue {
  type: sum
  sql: ${sale_price} ,,
  value_format_name: usd
}

measure: total_orders {
  type: count_distinct
  hidden: yes
  sql: ${order_id} ,,
}

• total_revenue: Standard sum calculation on the revenue column.
• total_orders: This counts a unique list of order IDs. If a single order has 5 items (5 rows), it will still only be counted once. We use hidden: yes because users don't need to see this by itself in an Explore, it's just a component for our final AOV calculation.

2. Create the Average (Ratio) Measure

Now, you can combine them using a special measure with type: number. This type allows you to conduct mathematical operations on other measures.

measure: average_order_value {
  label: "AOV"
  type: number
  sql: ${total_revenue} / NULLIF(${total_orders}, 0) ,,
  value_format_name: usd
}

Behind the Code:

• type: number: The crucial component. It lets Looker know not to apply its own aggregate function, but instead perform the division as written in the sql: block.
• sql: ${total_revenue} / NULLIF(${total_orders}, 0): You're now referencing measures, not dimensions. Looker is smart enough to generate the complex SQL needed to calculate both aggregates first and then perform the division.
• NULLIF(${total_orders}, 0): This is a safe division practice. If total_orders is zero, wrapping it in NULLIF prevents a "division by zero" error in your database, returning NULL instead of an ugly error.
• label: "AOV": The label parameter allows you to change the 'friendly' name of a field in the UI without changing its underlying LookML name, a useful trick for making fields like this more accessible.

Common Pitfalls and Best Practices

1. Respecting the "Grain" of Your Data

The "grain" of your data refers to what a single row represents. Does a row in your data table represent a single event, a product, an order with multiple items, or a daily summary? Understanding this is critical for correct averages. If each row is a line item, type: average will give you an average line item value, not an average order value. Always ask "What am I averaging?" before building your calculation.

2. Be Mindful About NULLs

Looker's average measures typically ignore NULL values. This is usually what you want, but be aware of how this might affect your dataset. If you have products that were never sold and their price is NULL, they won’t be included in your average product price calculations, which is correct. However, if a user's session time is NULL and you want that to count as a zero in your average, you would need to adjust the dimension's SQL directly with an IFNULL() or COALESCE() function beforehand, like COALESCE(${session_duration}, 0).

3. Using the Right Tool for the Job

Here's a quick cheat sheet:

• Best for permanent, standard metrics: Use LookML. type: average for column averages and type: number for ratios like AOV.
• Best for one-off analyses or quick insights: Use a Table Calculation. It's fast, flexible, and requires no LookML development.
• Best for Explorers who can't write LookML: A Custom Field can get a custom ratio (like AOV) done for a saved report without asking a developer for help, but it's not reusable across the whole platform like a LookML measure.

Final Thoughts

Mastering the different ways to calculate an average in Looker unlocks a deeper layer of analysis, taking you beyond simple aggregates. Whether you're building a reusable metric with LookML for the entire company, or just need a quick calculation for your personal report with a table calculation, understanding the right tool for the job is essential.

Getting these fundamentals right is valuable, but it involves writing LookML or building complex dashboards from scratch, which requires a steep learning curve. We built Graphed to simplify this entire process. You can connect your data sources in seconds and ask questions like, "What was our average order value by marketing channel last quarter?" in plain English. We turn your request into a real-time, interactive chart on a dashboard, letting you skip the code and get straight to the insights.