Creating a percent of total across rows with table calculations

[lindsey1]

Table calculations enable you to create calculations that operate on the table data returned by a SQL query. This is great for calculating metrics like percent of totals. This post shows how to create a percent of total across rows, and it assumes you are starting with a pivoted table.

You can also create a percent of total down a column.

There are a few ways to do this:

##1. Using pivot_row to calculate percent of total

As of Looker 3.36, we have introduced the pivot_row function, which allows you to aggregate over an entire pivoted row. Read more about using pivot_row here.

Let’s say I have the following data:

In order to create a percent of row total using pivot_row, I can create a table calculation like this:

${order_items.count} / sum(pivot_row(${order_items.count}))

Which will give me these results:

Using Value Format: 0.0% will format these values nicely for you. Read more about value format here.

##2. Using Look row totals to calculate percent of total

It is possible to reference Look row totals in table calculations. This is the simplest way to create a percent of row total. Row totals can be turned on in a Look by checking the Row Totals box:

Note that these totals are calculated in the SQL, so they may provide different results than adding up rows in table calculations

Let’s say you have a table like this:

In order to calculate a percent of total across rows using these row totals, you can create a calculation like so:

${orders.count} / ${orders.count:row_total}

Note that you must have the Row Totals enabled to reference the row_total variable.

Giving us these results:

Using Value Format: 0.0% will format these values nicely for you. Read more about value format here.

3. Using pivot_index to calculate percent of total

You can also use the pivot_index function in table cals to calculate the row totals manually, and then use that in your calculation of percent of total. This would be particularly relevant if you want the total to be a true addition of the values in the table, rather than being calculated in the SQL.

Let’s say you have this table:

You can create a percent of row total table calc like this:

${orders.count}/
(
coalesce(pivot_index(${orders.count}, 1), 0) +
coalesce(pivot_index(${orders.count}, 2), 0) + 
coalesce(pivot_index(${orders.count}, 3), 0)
)

This will use pivot_index to calculate the row total, and then divide each value in the table by that row total. Note that you will need to add to this for more than 3 columns. It will result in:

Using Value Format: 0.0% will format these values nicely for you. Read more about value format here.