How to exclude a specific pivoted column from table calculation?

  • 26 February 2021
  • 6 replies

Hello Looker community,


if I want to calculate a Mean of total across the whole row, EXCEPT the last column (80 or Above), what should I do? 

Mean (pivot_row(products Count)…???)



Best answer by Dawid 27 February 2021, 09:12

View original

This topic has been closed for comments

6 replies

Userlevel 3

Hi @KukHwa,

One way to compute operations on selected pivots columns is by using pivot_index.

This should give you the mean of all, but the last column.

pivot_index(${products.count}, 1)+
pivot_index(${products.count}, 2)+
pivot_index(${products.count}, 3)+
pivot_index(${products.count}, 4)+
pivot_index(${products.count}, 5)+
pivot_index(${products.count}, 6)+
pivot_index(${products.count}, 7) ) / 7


Userlevel 7
Badge +1

Another way is to use row’s total - pivot_where(${users.age} = “80”)

Thank you very much!

It works!

Both work perfectly!


But, I wonder if

there is any way to use mean function rather than manually computing total/number of values. I would need to calculate median, standard deviation for the selected pivoted columns as well, so I would prefer to use functions rather than manually computing.


Let me know!



Userlevel 7
Badge +1

I tested it on a similar scenario and achieved mean from the selection - 1 option in two ways:


mean(pivot_row(if(${users.age} = "80+", null, ${products.count})))


(${products.count:row_total} - pivot_where(${users.age} = "80+", ${products.count:row_total})) / (count(pivot_row(${users.age})) - 1)


Thank you so much!