Vistelio | Table Calculations vs LookML Measures: Choosing Where Your Logic Lives

Every Looker project ends up with the same quiet problem: a metric that lives in forty dashboards, defined slightly differently in each, because someone built it as a table calculation. This tutorial explains the three places logic can live in Looker, what each one is really doing, and how to decide.

The three places logic can live

LookML measures and dimensions. Defined in the model, computed in SQL by the database, governed by your Git workflow, and reusable everywhere.
Custom fields (custom dimensions, custom measures). Created in the Explore UI by users with the right permission. They compile to SQL, but they are saved with the content and not with the model.
Table calculations. Spreadsheet-style expressions evaluated by Looker in the browser against the result set that has already come back from the database.

The key difference is where the computation happens. Measures run in the warehouse over all matching rows. Table calculations run on the returned rows only, so they only see what the query returned, including any row limit.

What table calculations are good at

Table calculations are the right tool when the logic depends on the shape of the result, not on the underlying data:

Running totals and percent of total across the returned rows, using running_total() or ${orders.total_revenue} / sum(${orders.total_revenue}).
Row-to-row comparisons with offset() and offset_list().
Ranking and pivot-aware math such as pivot_index() and pivot_offset().
One-off exploration where you are not yet sure the metric deserves a permanent definition.

# Percent of total across the returned rows
${orders.total_revenue} / sum(${orders.total_revenue})

That expression is correct only if the result set contains every row you care about. Set a high enough row limit, or the percentages quietly become percent of the top 500.

Where table calculations go wrong

Row limits change the answer. Totals and percentages are computed on the truncated result.
Averages of averages. Averaging a ratio column across rows gives a different, usually wrong, number than a ratio of sums.
They do not travel. A table calculation belongs to one Look or dashboard tile. It cannot be used as a filter on the Explore, referenced by another field, or reached by the API in a model-aware way.
They are invisible to governance. No code review, no description, no deprecation path. See our guide to renaming and retiring LookML fields safely for why that matters later.
They cannot be used in access control or caching decisions because they never reach the database.

The ratio example, done properly

A conversion rate is the classic case. As a table calculation it tempts people into averaging.

measure: order_count {
  type: count
}

measure: purchasing_user_count {
  type: count_distinct
  sql: ${user_id} ;;
}

measure: conversion_rate {
  type: number
  value_format_name: percent_2
  description: "Purchasing users divided by all users in the filtered set."
  sql: 1.0 * ${purchasing_user_count} / NULLIF(${users.count}, 0) ;;
}

Because type: number measures reference other aggregates, the division happens after aggregation, so the ratio is right at every grouping level, including totals.

A decision checklist

Ask these questions in order:

Will anyone else use this number? If yes, put it in LookML.
Does it need to be filtered on, sorted by in the database, or used in another field? If yes, LookML.
Does it depend on row order, pivots, or the position of other rows? Table calculation.
Is it a one-time question from an analyst? A custom field or table calculation is fine, but tag it so it can be found later.
Does it appear in more than two dashboards? Promote it to the model.

Promoting a table calculation into the model

A repeatable path keeps the clean-up manageable:

Find candidates. Use System Activity to list the most-used content, then review the tiles with table calculations. Look for the same expression repeated across dashboards.
Translate the logic. Most arithmetic table calculations become type: number measures. Running totals can become a running_total measure, and percent of total can use percent_of_total.
Add a description and a label, and place the field in a sensible group_label.
Validate in Development Mode, then run content validation to confirm nothing references a field that no longer exists.
Swap the tiles over, then delete the old table calculation.

measure: total_revenue {
  type: sum
  sql: ${sale_price} ;;
}

measure: revenue_percent_of_total {
  type: percent_of_total
  sql: ${total_revenue} ;;
  direction: "column"
}

Note that percent_of_total has the same row-limit caveat as the table calculation version, because it is also a window over the returned result, so test with filters applied.

Where custom fields fit

Custom fields sit in the middle. They are SQL-backed, so they respect the whole dataset rather than the returned rows, and they let analysts answer a question without a developer. They are still unreviewed. A sensible policy is to allow them for exploration, but require a model change for anything that gets scheduled, embedded, or shared with an executive audience.

Governance tips

Add a naming convention for table calculations in shared content, for example a leading tc_, so they are easy to audit.
Review shared folders quarterly for repeated expressions.
Restrict the custom-field permission on production-facing roles if numbers must reconcile.
Keep a short "metric dictionary" in the model descriptions so analysts check it before inventing a new one.

Summary

Use table calculations for presentation-layer math that depends on the result set. Use LookML for anything that is a business definition. The cost of moving a metric into the model is small. The cost of fifty slightly different versions of it is not.

If your dashboards have drifted into too many one-off calculations and your numbers no longer reconcile, get in touch. A short model review is often enough to consolidate the metrics that matter.