Plotlines Value

[jt_]

Can I use a Looker Table Calculation as the value for a reference line in the Chart Config?

I'm trying to create a Reference line for both axis of a scatterplot. Currently the Looker UI only allows reference lines for the yAxis. To add one to the xAxis I have to go through the chart config, however as far as I can tell this only accepts a static value. Is there any way to add a dynamic value such as an average?

Thanks

[Kevin_F_McCarth]

Hello Jt_

I'm not sure if/how a dynamic vertical reference line can be achieved/supported via the advanced viz config.

But here is a demo of how I have traditionally achieved vertical reference lines leveraging a table calc for dynamic setting of the line location (of course, you'll need to update the logic to suit your needs).

Some callouts:

• You may wonder why the table calc includes: "+0*${basic_users.count}" . This trick (referencing a measure) will ensure that Looker sees this column as a measure not a dimension
• My calculation set value to 1 and used a separate axis to ensure the line always goes to top of the visual.  There could be other viable configurations which you might prefer, such as keep it on same axis, but set value to max(actual measure).

[jt_]

Hi Kevin,

Thank you for the suggestion. Unfortunately, the visualisation I am looking to recreate this in is a scatterplot where I want to see the relationship between two series. Given I have already selected the series for the X & Y axes, I am not able to place another on the xAxis to give me this line. 

All the best,

J

[Kevin_F_McCarth]

Hey J,

I’m not sure that your requirements limit the applicability of this solution, you likely just need to carefully configure the axes and series types.  Below I made an example with a pivotted series.  In this case I used sum(pivot_row([my measure])) to give me one measure-type table calculation per row rather than repeating the line for every series (which clutters the legend)

TABLE CALC IN THE ABOVE EXAMPLE:
if(${order_items.created_at_month}=date(2024,10,01),1+0*sum(pivot_row(${order_items.standard_count_measure}))
,0
)

[jt_]

Hi Kevin,

Thank you for the help. My set-up is a little different which I have attached below. Apologies I should have led with this context. You'll see I have 2 measures, Number of Sales and Gross Profit that I want to compare against each-other [These are made up measures for privacy sake]. Therefore I placed them both on a separate axis. Each datapoint here then represents a specific individual.  

Within the Looker UI I can go ahead and place a reference line for the mean on the yAxis (Number of Sales). What I am hoping to achieve is the same for the xAxis (Gross Profit). That is why I need it to dynamically update, as the end user will be able to control dashboard filters that will alter the timeframe so that they can look at specific days or multiple months combined. 
I'm not sure how I would implement your suggestion here as there is no option to add a third series to this graph.

I appreciate all your help,

J