# The Basics

Calculating a percent 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. In this article, I’ll show you a couple of ways to get percent-of-total calculations in Looker.

# For Queries Without Pivots

How you approach calculating percent-of-total is going to depend a bit on the shape of your query, so the following examples are going to assume that your query has no pivoted dimensions. If you’re working with a pivot, see the next section.

## Using Table Calculations

You can use table calculations to quickly generate an ad-hoc percent-of-total for a single measure across different values of a dimension.

The table calculation I used for this table is pretty straightforward. Referencing that measure on its own is as simple as writing `${order_items.count}`

, and this gives me the current row’s value for that measure.

Then, I can just divide that by the sum of that column by using the sum function:

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

## Using the Visualization

If you’re using a pie chart, you can actually skip the table calculation and let the visualization do the percent of total calculation for you. The percentages will be listed in the legend alongside the record that they belong to.

This won’t put the calculation in the table, so downloaded results won’t contain the percent-of-total. But if you’re just using the visualization then this should work just fine.

# For Queries with Pivots

If you need to do a percent of total across multiple series - either because you have multiple measures that you want to compare, or because you’ve pivoted a dimension and now have multiple measure columns - you have two options.

## Using Table Calculations

You may be tempted 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 percent of total separately for each of your pivots.

For this example, let’s assume we’re trying to find the percent-of-total for users in each of these five states, but split across gender.

What if you 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 of male and female users for that row. This also works for pivots with more than one category. If we pivoted here on State instead and the dimension was the created date, for example, we would still get a total across all states. It’s basically a *row total*.

The next step is a bit odd. We’re then 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})))
```

Yep. We’re summing the row total *again*. We can sort of treat the row total expression like its own column and sum it again to get the *grand total*. When you divide by the *row total* by the *grand total*, you get the percent-of-total for that row.

## Using the Visualization

If you only need the percent of total in your visualization and you’re using one of the following chart types, you can let the visualization do the percent of total calculation for you:

- Column
- Bar
- Scatterplot
- Line
- Area

Simply open the chart settings (on the right side of the Visualization tab under the Gear icon).

Here’s an example to show how the chart looks and acts. Mouse over each section of the column to see the percent of total for each state/gender combination.

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.

## Comments

0 comments

Article is closed for comments.