When we have a result set like the one below, we might want to compute the average for each state across the pivot columns. In Looker table calculations, this is achieved with the
To compute our average, we need to find the sum of the values across each row, and then divide that sum by the number of columns. We can do this by creating a table calculation with the example below.
An Elegant Solution
We'll take advantage of the
pivot_row() function to help us dynamically sum the row values across pivot columns and get the right column number.
pivot_row() function takes the values from all columns across a row and returns them in a concatenated list. For more detail, see this Help Center article on using
pivot_row() in table calculations.
First, we'll use
pivot_row() to get a list of all the values across each row:
Then, we'll generate an average by taking the mean of this list:
You can also use the second table calculation on its own, as it combines and performs both the
mean() operations on the values in the table.
Now, we have an elegant average of the pivot row, as shown above.