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!


48 replies

+1 for this feature

Just an update on this, folks - the Product team is still actively considering adding table calc totals and they are well aware of the level of demand that exists for this feature. It might be worth checking back with us in a few weeks as there may be a positive update by that time.

Userlevel 2

+1 on this 😎

Userlevel 1

Hey @Ezra_Wolfe,



I was sure to relay the feedback to the product team. Thanks for letting us know that this is something you’d like to see!



Cheers,



Leticia

As of Looker 6.2, table calculations now work with totals. See this documentation page for more information.

@marieb does the table calc have to be in a certain format/measure? I don’t see the feature working as expected (testing Looker 6.4)

Userlevel 7
Badge +1

What about it don’t you see working? The thing to remember with totals is that they run the same function as the column, but only over the totals row— So a mean() table calculation will return mean(total), not the sum of the values in that column.



Something like stdev() for example, which requires multiple values, will return 0, since it’s trying to execute over just the totals row/1 value. Does that click with what you’re seeing?

I created a table calc that is an integer multiple of a column in the results and expected the sum of the individual values in that column. from your response seems this should’ve still worked since integer*(sum of values) = sum of (values*integer), however I’m seeing a NULL result.



Perhaps, when no functional operators are used there should be a default behavior?

Userlevel 7
Badge +1

That use case does sound like it ought to work… I just tried 5.0*${table.count}and it’s correctly doing 5*${table.count:total} for the totals row. What’s the full formula you’re using?



The default behavior idea is not a bad thought— Like for your example, then by default it would be just a sum total (what most people think of when they think total). I’ll bring that up internally.



The question is, does it get too confusing to have different behaviors for the same feature? It’s hard to message that properly with a good UI/UX…

Ah, I see the issue. The Look I’m debugging is performing a dimension * integer (the dimension is a type: number)



I was able to work around this by:







  • didn’t work: created a table calc referencing the dimension, then another table calc referencing this. seems this produces a table calc that behaves like a dimension too for this feature.




  • worked: created a sum custom measure referencing the dimension, then created a table calc referencing this custom measure which accurately produced the sum of dimension * integer I needed


Userlevel 7
Badge +1

Aaah, that makes sense. Nice job!

Any update on this?  Not able to see any Table Calc totals 

any updates on this function? I really need this to work.

So if I’m reading @izzy’s comment correctly, the total of a table calculation column isn’t a sum of the column but rather applies the same calculation against the measure’s total? If so, i’m surprised this isn’t a feature yet… there should be an option to have the table calculation column’s total sum the values within its column. 

For example, I have a daily measure of orders and a table calc of the delta vs. the prior day. I’d like to see what the total delta for the period is. 

offset(${orders_daily.total_orders}, -1) - ${orders_daily.total_orders}

 

It’s hard to believe that I’ve been following this thread for 3 years and the original question/request was posted 4 years ago but there is still no solution.

 

Would be keen to get Looker’s view on why it has not been prioritized. My business users frequently work with the Table viz and the totals are all showing zeros. It’s taken me 3 years to motivate them to transition from Excel reporting to Looker reporting but we seem to have fallen at the last hurdle. They are already looking to go back to Excel which is such a shame because we have spent the last 3 years building this momentum.

Man this thread is old.. not sure if this would work:

 

Table calculation:

sum(sum(pivot_row( __ )))

+1. Similar to others, I’m trying to get people away from using Excel but this limitation is making it difficult.

+1 to others. Why do Table Calcs not show Row Totals in a Column Table Viz? 

 

+1, if it makes any difference..

Userlevel 1

+1 same situations as all others who have commented on this thread in the past 4 years.  Is there any update on a solution here or am I missing something?

+1 on this

Hello everyone. Same situation here, would like to have a way to get the total row of a table calculation. Thank you!

+1

Reply