How to filter a measure but still show totals

[lindsey1]

There are some cases where you only want to see rows where a measure returns above a certain value, but you still want to see the totals for all the values in a range. This can be done by filtering on a table calculation.

Note: the totals displayed in Looker are calculated in the SQL. Using this example, they will not necessarily reflect what is visible in the data table, which may be misleading. Be sure to inform users what data is being shown.

Let’s say I have the following Look with orders from the last 30 days with totals:

I want to only see days with orders greater than 50, but I still want to see the totals. If I filter on this measure in Looker, totals will not be displayed. This is to avoid showing misleading data.

To work around this, you can use table calculation filtering to only show rows where Count is greater than 50. When you do this, totals for the entire month will still be shown.

First, create a table calculation that tests for count > 50:

${orders.count} > 50

This will return Yes for rows where Orders Count is greater than 50, and No otherwise.

You can then filter out the No rows by selecting Hide "No"s from Visualization. Read more about this here.

Open the visualization tab and set it to table type. You will see only the rows where Count > 50, but the totals for the entire month will still appear!