Table calculations of totals and row totals?

Looker Q&A Looker
1

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!

7 Likes
2

Hiya Ethan,

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

Was that answer what you were looking for?

Thanks,
William

3

@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?

1 Like
4

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.

10 Likes
5

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

6

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.

1 Like
7

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

8

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

9

+1 for this feature.

10

+1 for this.

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

11

+1 for this as well

12

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

13

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

2 Likes
14

+1 would want to have this.

15

+1 as well

16

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

17

+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.)

18

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
  }

3e1daf8c860d793dfbb582d5748b63176bf5cf79.png
3e1daf8c860d793dfbb582d5748b63176bf5cf79.png1306×1420 112 KB

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.

19

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.

20

+1

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