How to filter a measure but still show totals


#1

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!


Filtering in Visualizations using Table Calculations (3.28+)
#2

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.


(Rajiv Singh) #3

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