How to filter a measure but still show totals

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!

3 6 4,772
6 REPLIES 6

ross2
Participant III

I attempted to use this solution on a data set that contains several thousand values in the secondary dimension, and it failed because of Looker’s enforced column limit. The browser window actually froze. My goal isn’t to display thousands of columns - I just want to top 100 entries - but I’m unable to get there.

To hide rows that are nulls and doesn’t effect the sum of other column:

Create a calculation
NOT is_null(${spend_platform.platform})

and click on gear icon:
image

how can I do this when I want to display only the top 5 highest numbers ( and the total), no matter what the value is? then that filter doesn’t work unfortunately.

Two options you could try:

  1. In the Data section set the row limit to 5 and order by your measure.
    Screenshot 2020-03-30 at 17.40.28

  2. Edit the plot and turn on limit displayed rows to show the first 5 rows. Again order by your measure to get the 5 highest rows.
    Screenshot 2020-03-30 at 17.37.36

The total will still be the total of all rows in your query.

Thank you, this worked 🙂

This feature is great to help with visualizations, but you’ll notice that the Total row doesn’t reflect just the rows that are visible (e.g. the two rows are 51 and 54, but total is 1096). Is there a way to get the Total to only add up what is visible?

Top Labels in this Space
Top Solution Authors