Creating More Complex Reference Lines
Many Looker visualization types can be customized by adding a reference line with out-of-the-box edit visualization settings:
The out-of-the box reference line options include static value, median, average (mean), maximum, minimum, and range, which can be selected in the Edit Visualization settings. However, you may be looking to represent a more specific metric, such as a weighted average, with a reference line, instead of the out-of-the-box options. We can make custom reference lines by creating table calculations and then plotting them in visualizations.
Continuing with the weighted average example above, let's say you you wanted to weight average user age by the count of users, and had measures for each. You could add both the users.count
measure and users.average_age
measure to an Explore. Then, you could create the following table calculation to calculate the weighted average with the two measures:
mean(
sum(${users.count}*${users.average_age})
/
sum(${users.count})
)
This table calculation will return a single value — the weighted average — for every row of the query.
If the selected visualization type is not a line chart, such as a column chart, you can visualize this table calculation as a line by selecting the line visualization type for the table calculation series on the series tab of the edit visualization settings. Since the table calculation only returns one value, the line will appear as a horizontal reference line.
If you do not want users.average_age
to appear in your visualization, you can also hide that field using the Hide from Visualization option.
The resulting visualization will appear something like this:
Alternatively to creating a table calculation between measures in an Explore, you can hard code a weighted average measure in LookML as detailed in the Help Center article How to Make a Weighted Average.