Subtotals with Table Calculations

subtotals
tablecalcs

(Sean Higgins) #1

Subtotals are handy for a quick summary of grouped items when also viewing from a higher level e.g. subtotal of sales by Brand while also viewing the overall inventory sales. Although this article is pretty long, the solution is actually very simple, I’ve just broken it down.

Note: This is a stopgap solution, so if you haven’t already, check out @andy’s awesome article on generating Subtotals with a CROSS JOIN pattern here. It provides more flexibility and allows you to do drills and all sorts of lovely Looker legerdemain (magic).

Example:
Let’s use the situation I 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 i.e. 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’re looking at represents:

Nothing fancy, it just starts back counting 1,2,3... whenever the Brand changes.
Here’s 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)

All that’s happening here is we’re using match() to check to find the row value for the first unique instance of each Brand value. Shout out to @davids for this nice pattern.

Step 2 - Now we can use this partition_row_number as the basis for our Subtotal. The reason we need this is because the amount of rows per Brand is going to change. See the !it Jeans rows vs a Brand like Calvin Klein.

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)

What’s key here is that we’re using the offset_list() function to get all the rows that match the current row’s Brand value, and we’re offsetting it using the partition_row_number to make sure that 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 how @Jiro did this in his article on Weekly totals here.

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})

Subtotals on row with calculated dimension
Group By Table Calculation in Visualization
Creating a running total down columns with table calculations
How do I count duplicate values in a table?
(Brecht Vermeire) #2

If you’re looking to get the sum per group, similar to a SQL window function, the following pattern could be useful. In the example below, we are using ${products.brand} and ${products.item} as dimensions, and ${orders.count} as measure, and are looking to get a percent of group total.

First we need to find the group start row and end row:

  • group start row:
match(${products.brand}, ${products.brand})
  • next group start row:
count(${products.brand}) - match(${products.brand}, offset(${products.brand}, count(${products.brand}) - row() * 2 + 1)) + 2

Once we have these, we can get the sum per group (brand).

  • group sum (analogous to SQL’s SUM(column) OVER (PARTITION BY group)):
sum(offset_list(${orders.count}, -1 * (row() - ${group_start_row}), ${next_group_start_row} - ${group_start_row}))

And then finally, for your percent of group total:

${orders.count}/${group_sum}

(Naseha Khan) #3

This worked perfectly! Okay now to add a new question, what if instead of Subtotal, I needed the average?


(Deepika) #4

Hi Naseha_Khan

We can use any aggregation function here just by replacing ‘sum’ with the required function. In this case, we can use ‘mean’ for getting the average. So something like the following should do the trick:

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

Thanks
Deepika