Subtotals with Table Calculations

[seanhiggins]

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

Starting in Looker 21.12, you can use Quick Calculations to perform common calculations on numeric fields that are in an Explore's data table without using Looker functions and operators when the Custom Fields Labs feature is enabled, and you have permission to use them.

This article shows a method to generate subtotals with a standard Looker table visualization. In Looker 6.10, the Table-Next visualization was introduced, which can generate subtotals natively. For more information, see this documentation page.

Subtotals are handy for a quick summary of grouped items, when you are also viewing from a higher level, such as subtotal of sales by brand, while also viewing the overall inventory sales. This article will show you a breakdown process on how to accomplish this.
 

This is a stopgap solution, so check out this awesome Community post about generating Subtotals with a CROSS JOIN pattern. It provides more flexibility and allows you to do drills and all sorts of lovely Looker legerdemain (magic).

Example

Let's use the situation described above. We have Brands, within which there are Categories and we have a Measure, which computes the Total Sale Price or revenue. This is our dataset:

Our Brand managers want to see just a quick reference, for how much that Total Sale Price is per Brand. For example, they want to see a Subtotal for each Brand.

Using Table Calculations, we can create a 4th column, that will show this inline with the last row of each Brand:

We do this in a few steps, all of which are pretty simple.
 

Solution

Step 1: Create a Table Calculation that indicates which row number, within each brand, the particular row you are looking at represents:

It starts back counting: 1,2,3... whenever the Brand changes.

Here is the Table Calculation for that:

if(match(${products.brand},${products.brand})=offset(match(${products.brand},${products.brand}),-1)
 , 1+row()-match(${products.brand},${products.brand})
 , 1)

Here we are using match() to check and find the row value for the first unique instance of each Brand value.

Step 2: Now, we can use this partition_row_number as the basis for our Subtotal. We need this because the amount of rows per Brand is going to change. See the !it Jeans rows vs a Brand like Calvin Klein (in the image above).

For our Subtotals column, this is the Table Calculation used:

if(
 NOT(${products.brand} = offset(${products.brand},1)),
 sum(offset_list(${order_items.total_sale_price},-(${partition_row_number}-1),${partition_row_number})),null)

The Key here is that we are using the offset_list() function to get all the rows that match the current row's Brand value, and we are offsetting it using the partition_row_number, to make sure of that match, even if the Brand changes in the data, or we introduce a new dimension. The sum() will still only take the number of rows calculated by our first Table Calculation, and we can repeat this pattern for other Measures if we want, as well as changing the dimensionality:

• By Country:

  

• By Age Demographic:

  

  This is actually very similar to this Community post on Weekly Totals.

  Extra Credit

  
  If you want to group those Brand fields together, you can use a Table Calculation for that too!

  if(${products.brand} = offset(${products.brand},-1),"",${products.brand})