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:
- 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
- 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 😉 ), 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.
