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.

[KKSU]

New to looker. Wondering why 3 columns table need to pivot_offset 5 times??

Thanks for help.

[lindsey1]

In the final example above, it uses pivot_offset as a workaround for calculating the row total in Looker versions that did not allow referencing the row total in table calcs. So if you are able to reference row totals in table calcs, I would recommend doing that instead. (I’ve added a note for that above).

In the workaround, it uses pivot_offset 5 times to ensure that all columns are added up. Since pivot_offset is relative to the location of the current column being evaluated, you need to add up the current column and two columns both to the left and right to ensure all columns are added in all situations. That’s why are there five lines in the example.

We also introduced a new function called pivot_index (more info here). That would be a simpler way to do what the pivot_offset example does above. I’ve added that example to the article above!

[lindsey1]

As of Looker 3.36, we have introduced a pivot_row function, that allows you to perform functions over an entire pivoted row, without having to know how many columns there will be. pivot_row can be used to calculate a percent of total across rows.

Let’s say you have this table:

You can create a percent of row total using pivot_row like this:

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

This uses pivot_row to select the entire pivoted row of ${orders.count}. It then sums over that row to calculated the row total. Finally it divides each value of ${orders.count} by that row total. This will result in:

[robin_zhang]

Thanks! It’s helpful! Can I get the percentage for the last low? I mean for the total one.

[rachel_johnson]

Hey @robin_zhang,

At this time table calculations do not appear in the totals row since the queries row is calculated through a separate query. I can definitely let the product team know this is something you’d like to see!

We can still get the percent of each tier’s total by using table calculations to get the sum of the column and dividing that by the total sum of all columns which will create a new column to the right of the pivots.