# Creating a percent of total across rows with table calculations

**URL:** <https://discuss.google.dev/t/creating-a-percent-of-total-across-rows-with-table-calculations/117430>\
**Category:** Looker\
**Created:** [July 1, 2015, 8:40pm UTC](https://discuss.google.dev/t/creating-a-percent-of-total-across-rows-with-table-calculations/117430 "2015-07-01T20:40:35Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![lindsey1](https://d3byx8b92xw3lq.cloudfront.net/user_avatar/discuss.google.dev/lindsey1/32/173850_2.png) [@lindsey1](https://discuss.google.dev/u/lindsey1)\
**Post date:** [July 1, 2015, 8:40pm UTC](https://discuss.google.dev/t/creating-a-percent-of-total-across-rows-with-table-calculations/117430/1 "2015-07-01T20:40:35Z")

</div>

[Table calculations](https://discourse.looker.com/t/table-calculations-3-18-labs-feature/636) 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](https://discourse.looker.com/t/creating-a-column-percent-of-total-with-table-calculations/1054).

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](https://discourse.looker.com/t/aggregating-across-rows-row-totals-in-table-calculations-3-36/1894).

Let’s say I have the following data:

 ![e6310d21de3248a047050d91798148984437ea1c.png](https://d2yds90mtvelsl.cloudfront.net/original/3X/0/2/0229bae80d7db1227f00cdb61c3f02dfcee5f6da.png)

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

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

```

Which will give me these results:

 ![2c6db3101db55d5b3c00e335261db603e2a251ee.png](https://d2yds90mtvelsl.cloudfront.net/original/3X/3/4/34cc8ea61dac11419e195d079eece612543649f7.png)

Using **Value Format:** `0.0%` will format these values nicely for you. Read more about value format [here](https://discourse.looker.com/t/formatting-table-calculations-with-value-format-3-22/884).

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

It is possible to [reference Look row totals in table calculations](https://discourse.looker.com/t/how-to-refer-to-totals-and-row-totals-in-table-calculations-3-24/1055). 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:

![147cd505b45e49d2fad0e64156edb19f12f6c19b.png](https://d2yds90mtvelsl.cloudfront.net/original/3X/9/0/902f5171514bc9d30714e42a46957b4ad1ea3f7c.png)

> 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:

 ![5a43bd580af10f1d4af81fa6745eaa2505472163.png](https://d2yds90mtvelsl.cloudfront.net/original/3X/c/9/c999e7edfbfb2433b77bc9d0cf91965bd0d74927.png)

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

```auto
${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:

 ![5c0e228eebdb1eecc299aba35ec94f4c5a7a1218.png](https://d2yds90mtvelsl.cloudfront.net/original/3X/e/e/eefecad21ccb3092c2279aa4c07e5cac74c7e4a0.png)

Using **Value Format:** `0.0%` will format these values nicely for you. Read more about value format [here](https://discourse.looker.com/t/formatting-table-calculations-with-value-format-3-22/884).

## 3. Using `pivot_index` to calculate percent of total

You can also use the [`pivot_index`](https://discourse.looker.com/t/using-pivot-index-in-table-calculations-3-28/1291) 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:

 ![0811eff8932174e8f6be5c21477ff91cc09b0f1e.png](https://d2yds90mtvelsl.cloudfront.net/original/2X/4/429aa7997299b28417e60a3b4118b1724d08777a.png)

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

```auto
${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:

 ![b4f82de0e6c3ca986085edfb80e994f47ade71dc.png](https://d2yds90mtvelsl.cloudfront.net/original/2X/e/e481fcccc7fda7fd21c7762b35cf2fc924e57127.png)

Using **Value Format:** `0.0%` will format these values nicely for you. Read more about value format [here](https://discourse.looker.com/t/formatting-table-calculations-with-value-format-3-22/884).

---

<div class="post-metadata">

**Author:** ![anonymous8](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@anonymous8](https://discuss.google.dev/u/anonymous8)\
**Post date:** [November 4, 2015, 7:19pm UTC](https://discuss.google.dev/t/creating-a-percent-of-total-across-rows-with-table-calculations/117430/2 "2015-11-04T19:19:55Z")

</div>

New to looker. Wondering why 3 columns table need to pivot\_offset 5 times??

Thanks for help.

---

<div class="post-metadata">

**Author:** ![lindsey1](https://d3byx8b92xw3lq.cloudfront.net/user_avatar/discuss.google.dev/lindsey1/32/173850_2.png) [@lindsey1](https://discuss.google.dev/u/lindsey1)\
**Post date:** [November 4, 2015, 7:47pm UTC](https://discuss.google.dev/t/creating-a-percent-of-total-across-rows-with-table-calculations/117430/3 "2015-11-04T19:47:03Z")

</div>

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](https://discourse.looker.com/t/using-pivot-index-in-table-calculations-3-28/1291)). That would be a simpler way to do what the `pivot_offset` example does above. I’ve added that example to the article above!

---

<div class="post-metadata">

**Author:** ![lindsey1](https://d3byx8b92xw3lq.cloudfront.net/user_avatar/discuss.google.dev/lindsey1/32/173850_2.png) [@lindsey1](https://discuss.google.dev/u/lindsey1)\
**Post date:** [December 23, 2015, 7:40pm UTC](https://discuss.google.dev/t/creating-a-percent-of-total-across-rows-with-table-calculations/117430/4 "2015-12-23T19:40:27Z")

</div>

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:

 ![95a2d30f8c073dc84ab5a8baad60971c82c86844.png](https://d2yds90mtvelsl.cloudfront.net/original/3X/1/a/1a070e7d8b4c380663000acc6869f16808e6c007.png)

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

```auto
${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:

 ![fd98a219422cc571f959dd9f560b9fe70884e601.png](https://d2yds90mtvelsl.cloudfront.net/original/3X/2/6/26a7accd7d79e6df123a0d61c898bfb66cf2e90b.png)

---

<div class="post-metadata">

**Author:** ![anonymous8](https://avatars.discourse-cdn.com/v4/letter/a/7cd45c/32.png) [@anonymous8](https://discuss.google.dev/u/anonymous8)\
**Post date:** [December 21, 2017, 8:21pm UTC](https://discuss.google.dev/t/creating-a-percent-of-total-across-rows-with-table-calculations/117430/5 "2017-12-21T20:21:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![rachel\_johnson](https://avatars.discourse-cdn.com/v4/letter/r/ce7236/32.png) [@rachel\_johnson](https://discuss.google.dev/u/rachel_johnson)\
**Post date:** [December 22, 2017, 1:41am UTC](https://discuss.google.dev/t/creating-a-percent-of-total-across-rows-with-table-calculations/117430/6 "2017-12-22T01:41:57Z")

</div>

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.