Question

Percent of subtotals

  • 12 July 2017
  • 7 replies
  • 785 views

Is there a way to do a percent of a subtotal in a table calculation or LookML?


7 replies

Hey @carlyk! Thanks for the question. You can reference the Totals and Row Totals fields in a table calc by appending :total or :row_total to the field in the substitution operators like so:


${view_name.measure:row_total}


The percent formatting options are available via the drop-down in the table calc:



The Explore Results look like:


Hey @jonathon – just getting started with Looker here, and found this thread very helpful.


As a follow-up, is there a way to get looker to also calculate the percent of the row total for the summary row? For example, the “No” total column is 25,278 and the “Yes” total column is 798, but the percent of the row table calculation is blank for the totals. Is there a way to get that to fill in, or can it be populated with a separate table calculation?


Thanks

Hey @bmadres,


We’re excited you’re already building stuff with Looker! We currently cannot display totals of table calculations. One of the main reasons for that is the confusion it might create in whether they’re totals for the series above or for the measure totals. It’s definitely on the radar of the product team, and I’ve added your feedback to that as well.


Meanwhile, one way to get around it is to sum all the counts and get a percentage out of that. I’ve added the code below. It won’t be ideal because you’ll end up with an entire column of the same value. If you’d like any additional help with working towards a different result, please send us an email at help.looker.com, and we’ll be happy to assist.


sum(pivot_where(${order_items.returned} = yes, ${products.count}))/ sum(pivot_where(${order_items.returned} = no, ${products.count}))

Hi @talal.naboulsi @jonathon,


Is it on the Looker roadmap to enable % to total for subtotals and basically make it dynamic? For instance, in the retail world, we want to know the percent contribution of a store’s sales to the total sales of a district that could be made up of, let’s say 10 stores. If we’ve filtered on only 2 stores in that district, we’d want the subtotal to accurately reflect the percent contribution for only those 2 stores to the total district sales. Unless we’re missing it, there currently doesn’t seem to be any way to do this in Looker and have it flexible enough to swap out dimensions on the fly. For example, if I then wanted to look at district sales as a percent of region, which is the next level up.

Userlevel 7
Badge +1

If I’m understanding you right, then I think you’re correct— It’s not possible to dynamically refer to just “that other field” in a query, whatever it may be. Regardless of if you’re making a measure, or using table calculations, you have to explicitly reference the field.


There are some ways to make things more dynamic, like creating a dynamic dimension using the same logic as here: https://help.looker.com/hc/en-us/articles/360001288468-Dynamic-Timeframes-for-Dimension-Groups.


But, I’m not sure how we’d implement a full feature that does what you’re suggesting. Perhaps you could define subtotals such that column 1 was always the total grouping and column 2 was always the subtotal grouping, but it could get convoluted. Interesting!

Hi Looker team, I'm trying to create percent of subtotal. Is it possible?

In the example below I would like to present the share of each origin country from location name (yellow).

 

 

Reply