Knowledge Drop
Last tested: Mar 4, 2020
ERROR: Cannot subtotal when filtering on measure "orders.count". Subtotals not calculated.
The reason it breaks is that the count(*) of the measure is dependent on the dimensions selected in the query.
The easiest way to understand how subtotaling works is how Redshift generates the SQL (it varies by database support). Subtotaling is very similar to totals!
In your explore, you may have selected customer name, product name, order_id, and order total. The base query Looker creates is very familiar to everyone with "Select customer_name, product_name, order_id, sum(sales) as order_total".
When you enable subtotals, Looker appends onto the query each rollup. These queries detract one dimension for each rollup. So for our example, you will see "Select customer_name, product_name, sum(sales) as order_total" AND "Select customer_name, sum(sales) as order_total". These rollups are appended onto the bottom of the base query as UNION ALLs. Looker's rendering engine handles how to present the information.
If you are attempting to utilize a having filter (i.e. filtering on a measure) it wouldn't make sense because as you remove the dimension, the having filter does not make sense. If I was asking about orders with sales greater than $100 when I ran my rollup queries Looker could apply the having filter, but it would bring in more order IDs (because by product_name sales > 100 is probably super common).
SO HOW DO I FIX THIS?!
Good-ole' derived tables (ideally NDTs). If you have Looker create an order total for orders, then you can filter on that order total as a dimension instead of the measure. Enable Bind All Filters if applicable!
Steps would be to create the dataset that the customer wants to perform the final measure filter on. Create the NDT from the Explore and then model it to the Explore. Surface only that Order Total dimension and filter on it for the subtotaling to work.
Alternatively you can also use the subtotal custom visualization.
LinkedIn
Twitter