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.