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.

[{ "type": "thumb-down", "id": "hardToUnderstand", "label":"Hard to understand" },{ "type": "thumb-down", "id": "incorrectInformationOrSampleCode", "label":"Incorrect information or sample code" },{ "type": "thumb-down", "id": "missingTheInformationSamplesINeed", "label":"Missing the information/samples I need" },{ "type": "thumb-down", "id": "otherDown", "label":"Other" }]
[{ "type": "thumb-up", "id": "easyToUnderstand", "label":"Easy to understand" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"Solved my problem" },{ "type": "thumb-up", "id": "otherUp", "label":"Other" }]