Measures that can refer to larger aggregations


(Laurent Stanevich) #1

I need to create aggregated measures that compare a measure’s value within a given cell against a larger contextual aggregation. The simplest example is a percent_of_total_sales measure that just takes the sales_sum measure for a given cell, and divides it against the overall total sales_sum for the entire table, so that I could go from values like this:

to

Obviously, that’s not all I’m just looking for – what I really need is the equivalent of the DAX ALL()-related functions, that allow you to selectively ignore any given dimension used in an explore.

From what I’ve been able to find so far, you can do something like this with ‘positional functions’ in table calculations, but that falls short on at least two important fronts:

  1. I need to be able to use these measures freely in multiple explorers and dashboards, so adding and maintaining the same table calculations into every single one isn’t really an option, and
  2. I’m actually looking to use these to impose custom conditional formatting, using HTML and Liquid , along the lines of this post, but where the length of the “progress bar” in each cell could be proportional to how it compares to its overall column, row or table. There’s no real way to refer to the output of table calculations for that, since those table values live “above” any centrally-scoped measures, correct?

I’m fairly certain that I could get what I need through some intricate SQL-fu involving subqueries and/or aggregated derived tables (and if that’s the case, I’m using PostgreSQL :wink: ), but is there any more straightforward way to expand a measure’s scope? (And if not, can I register a strong upvote on that, as a feature request?)

Thanks in advance for any help – we just signed our contract yesterday, but I’m already very, very pleased and impressed with what we’ve been able to do, already.


(Peggy Beard) #2

Hi Laurent!

For the simple example, you probably want to check out the percent_of_total measure type in our documentation. It may not cover all of your use cases, but it’s a great place to start.

Thanks for the nice words!

Peggy
Looker Department of Customer Love


(Laurent Stanevich) #3

(Thanks so much for the quick response on this – I thought I had set up my notifications correctly, but I didn’t see this until I came back to check.)

In any case, that measure’s definitely a good start, and it’ll probably often get me most of where I need to go. In the meantime, can I maybe suggest providing _of_total versions for all aggregate measures (esp. sum), with universally availabledirection: column, direction: row and direction: table options?

UPDATE: Sorry, typed in the wrong suggested parameter – corrected it from dimension: ... to direction: ....


(Ryan Dunlavy) #4

Hi @LStanevich,

I can definitely pass that along to the product team as a built-in function!

Cheers,
Ryan


(Laurent Stanevich) #5

Thanks so much, @ryan.dunlavy. I’ve updated my suggestion, btw, to use the correct direction parameter.