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.

The Basics

Calculating a percentage of the total for a given row in a table can put potentially large numbers into context for an analyst, helping to see how one small piece contributes to the whole. This article shows a couple of ways to get percent of total calculations in Looker.

For Queries Without Pivots

How best to approach a percent of total is going to depend a bit on the shape of the query, so the following examples assume the query has no pivoted dimensions. In the case of working with a pivot, see the next section.

Using Table Calculations

Using table calculations quickly generates an ad-hoc percent of total for a single measure across different values of a dimension.

The table calculation used for this table is pretty straightforward. Referencing that measure on its own is as simple as writing ${order_items.count} , and this gives the current row's value for that measure.

We can then just divide that by the sum of that column, by using the sum function :

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

Using the Visualization

When using a pie chart, there is no need to use the table calculation; just let the visualization do the percent of total calculation by itself. The percentages will be listed in the legend, alongside the records to which they belong.

This won't put the calculation in the table, so downloaded results won't contain the percent of total. But if we are just using the visualization, then this should work just fine.

For Queries with Pivots

When you need to do a percent of total across multiple series, either because there are multiple measures that need to be compared, or because there is a pivoted dimension and you now have multiple measure columns, there are two options:

Using Table Calculations

It can be tempting to use the straightforward technique that you can use for unpivoted queries to get a percent of total for pivoted queries. While that definitely still works, it's going to calculate a separate percent of total for each of the pivots.

For this example, let's assume we're trying to find the percent of total for users with the five most recently placed orders, but split across age tier, to see the breakdown of the users placing those orders:

What if we want to calculate a percent of total across the whole row? There are a few table calculation functions that are designed to help us pull values out of a specific pivoted column, and we can use one of them, called pivot_row , to group all of the pivoted row values together as a list. Many functions can take a list as an argument, including sum . This will let us squeeze the list of values down into a single number:

sum(pivot_row(${users.count}))

The result here is an expression that gives us the total number of users for that row. This also works for pivots with more than one category. It's basically a row total .

The next step is a bit odd. We're going to take the row total and divide it by the sum of the row total. Here's what it ends up looking like:

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

Correct! We are summing the row total again . We can treat the row total expression as if that expression were its own column, and sum it again to get the grand total . When we divide the row total by the grand total , we get the percent of total for that row.

Using the Visualization

When we only need the percent of total in the visualization and we are using one of the following chart types, we can let the visualization do the percent of total calculation for us:

• Column
• Bar
• Scatterplot
• Line
• Area

Simply open the chart settings (on the right side of the Visualization tab under the gear icon):

Here is an example to show how the chart looks and acts. We can mouse over each section of the column to see the percent of total for each order created date/age tier combination in the tooltip:

It's important to note that these percentages won't make it into any non-interactive medium. So, if you need to export the data or a static image of the chart, you'll probably want to use a table calculation instead.