When you select the option to display totals in Looker, in certain circumstances a total will be returned that does not match the total you expected. Unlike on a spreadsheet, Looker's totals do not calculate a simple sum over the returned rows or columns in the report. Instead, Looker totals the underlying data — it executes that measure's aggregation over the underlying data, without the grouping into rows that the Look or dashboard shows. This can lead to a discrepancy between the data totals and what was expected.

# Why Totals Can Be Lower

Let's start with an example. Say we are counting the number of distinct users who bought a certain category of product over a period of time:

The SQL used to create this table looks like this:

SELECT products.category AS `products.category`, COUNT(DISTINCT users.id ) AS `users.count`

Notice that if we tally up each of the categories ourselves, we get a number much higher than the total reported by Looker. Because each user can make purchases from multiple categories of products, when we add up each row ourselves, we are double-counting some users. But Looker uses a SQL query to get the count of distinct users and avoids double counting.

The SQL used to calculate this total looks like this:

SELECT COUNT(DISTINCT users.id ) AS `users.count`

Notice that Looker uses the SQL from the `users.count`

measure to calculate the total.

Any filters and joins we add will also be included in the totals query, which will keep the total consistent as the query changes.

# Why Totals Can Be Higher

There are two situations where Looker totals can actually be higher than the totals we get by adding up the rows: when the query is filtered on a measure and when the query is hitting the row limit.

- When the query is filtered on a measure, our totals are being calculated
*before*these filters/restrictions are applied. - In the case of row limits, the total query that Looker creates isn't subject to those limits, because the total is only ever going to return a single row.

To calculate totals on just the values in the table, we can create a new column using table calculations. Here are some examples.

For a column total:

sum(${view_name.field_name})

For a row total:

sum(pivot_row(${view_name.field_name}))

Note: This approach will generate a new column, and not appear as a native "Total" row or column.