Aggregating across rows (row totals) in table calculations

You can use shortcut calculations to perform common calculations on numeric fields that are in an Explore's data table without using Looker functions and operators when you have the permissions to create table calculations.

There may be situations where you or other users want to analyze pivoted data in Explores. This page discusses how you can use the pivot_row() table calculation function to create row totals and other aggregations that can help users gain further insight into Explore data.

How does pivot_row() work?

The pivot_row() table calculation function groups all the values of a pivoted row into a list.

This example uses the following Explore data table with Products Count grouped by Orders Created Date and pivoted by Users Age:

You can use pivot_row() to write a table calculation that will group all of the Products Count row values into one column:

pivot_row(${products.count})

The table calculation then outputs a new column that displays a list of all the values of Products Count in each pivoted row:

Calculating the row total using pivot_row()

You can also perform calculations on the lists that you create with the pivot_row() function to make further analyses. For example, you can calculate row totals by using the sum() function on the listed values that are returned by pivot_row().

Using the same Explore data table with Products Count grouped by Orders Created Date and pivoted by Users Age, you can write the following table calculation to output the row totals for Products Count:

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

The table calculation sums the list of Products Count values that are calculated with the pivot_row() function and outputs a new column that displays the total of each pivoted row:

Calculating other aggregations using pivot_row()

You can use the pivot_row() function to aggregate across any pivoted row. For example, rather than a sum(), you can also calculate the mean() of the values in the list, the max(), the min(), and so on. This section of the Looker functions and operators documentation page lists all the available table calculation functions and operators.

You can also perform more complex calculations across pivot rows with the pivot_row() function, including a percent of total. Read about how to use pivot_row() to calculate a percent of total across rows in the How to calculate percent-of-total Best Practices page.