Question

Table calculations of totals and row totals?


Userlevel 2

Question:


In past I know that table calculations weren’t allowed for totals. See [retired]Why don't my table calculations have totals? .


I don’t quite see why table calculations couldn’t simply work on total rows in the same way they work on regular rows. Is it because some table calculation formulas would work on regular rows but return misleading results on total rows? I could see the mixing of null and non-null values being tricky for calculations using mean() and others, but SQL has the same issues and deals with them. Admittedly, sometimes the way SQL deals with them is very confusing to users and I could imagine that Looker would prefer to err on the side of caution.


Or has this changed?


Thanks!


40 replies

Hiya Ethan,


See my answer in Why don't my table calculations have totals?.


Was that answer what you were looking for?


Thanks,

William

Userlevel 2

@William_Lane No, what I’m asking is this: if you click ‘Totals’ in the UI, you get an additional row on the bottom, the totals row. So, why can’t a table calculation (I’m thinking here of a table calculation which doesn’t use any “:total” fields) operate on that row in the same way it operates on every other row?

Userlevel 3

Hey Ethan - I think it’s because measures are not guaranteed to be additive or follow line behaviour at the totals row. However i do agree it would be great if there were an option to ‘apply on total row’ (with a warning) so that the same calculation could be applied at the total row level.

Hi Ethan,


This is great feedback for the product team and clearly from Alex’s comment you aren’t the only one who is interested in this topic. Building a little on Alex’s and William’s comments here is some more background on what our product team is facing in this area:


Since row totals are a different series in and of themselves, we can’t have table calcs necessarily “continue” on to apply to the totals row without causing quite a bit of confusion. i.e is that value a total of the table calc column or is it the table calc applied to the row totals? In most cases these would be very different numbers

Was also just looking for this and surprised it’s not possible. I can see the challenges with regards to implementation, however I think this is a pretty essential feature with some very basic use-cases. There should be an option that lets me chose how the table calculation should be computed for the totals and they should probably be turned off by default.

Userlevel 2

Hey @ceik,


We appreciate the feedback. This feature has been on our Product team radar for some time. The challenge here is that totals mean two different things. We can’t do a unique total with a table calc, so we’d have a lack of parallelism between the two totals which feels problematic to us. However it is something our Engineering team is looking into. Hope this clarifies it for you.


Regards,


Sasha

Adding my vote that this would be a very useful feature.

+1 for this feature.

+1 for this.


Are there any updates on progress / work-arounds / or other fixes on this?

+1 for this as well

+1 from me as well. Do we have any updates from the Engineering team since July of last year? Thanks!

Userlevel 3
Badge

Hi Andrew,

No updates from engineering/product but all of the +1s here are being considered. Thanks for checking in!

+1 would want to have this.

Userlevel 1

+1 as well

+1 here as well, this is a very commonly requested capability

+4 from WB/TBS as well, (I am building dashboards for four separate clients so far, and three ask for this everytime they see a table without total calculations, and one is about to get their first table without total calculations.)

One other approach is to take the values out of the table calculations and put them into the base view. I was previously doing some table calculations to determine Week over Week growth by offsetting rows, etc.


To get around the limitations of the table calculations though, I have added the last week numbers inline with the current numbers, and then added the percentages to the view. These percentages get calculated across all the rows including the cumulative total rows.


  measure: weekly_sub_growth {
type: number
sql: (${total_paid_subscriptions}::numeric/${total_lastweek_subscriptions})-1.00 ;;
value_format_name: percent_1
}


There are often ways to work around the limitations of Looker if you take the time to materialize the data as you need it displayed.

Hi John- looks like you’ve made a good use case here! I’ll also add your previous +4 to the product team’s list for this request.


Cheers.

+1


this is an essential feature and hope to have it available soon

+1 from me

+2 from my engineers who wish Looker had this feature

+3 from my analysts who avoid using Looker because it lacks features like this

Thanks, @jbroberg, I’ve passed along your feedback to the Product team. And thank you for the details on who in your company is interested in this feature.

I’ll add my +1 to the pile.

+1. Analysts here ask about this regularly.

Adding a +1 in hopes this gets prioritized

+1 here.


Astonishing that this has been open so long. Seems like a very small thing to implement.

Reply