How can I create a reference line that adjusts based on drop-down list selection in Google Data Studio?

Question

I have a bunch of key measure graphs, for this example, I will use the Safety key measures.

• To add a goal line to a bar graph
• I created a parameter called Safety Goal.
• My data type is number (decimal),
• my permitted values are Any values
• my default value is 0.
• On the Chart > Bar > Style > Reference Line #1
• Type = Parameter
• the rest is left to the defaults
• Also created a inputbox with control field set as this parameter "Safety Goal" This works if I type in a number in the input box, I receive my goal line. How can I set this up to create a goal-line based on a selection in a drop-down list?

The Data Studio Minimal reproducible example can be found at this link https://datastudio.google.com/reporting/3b476f9a-e79b-4421-b755-a086d9c8e7f5/page/LfniC The data source link is https://docs.google.com/spreadsheets/d/1RJg16hWB_KfmTuDJAYgyrw_TVIK1LGRKY6qMGDE-5nk/edit#gid=1090568723 This link can be found on the Google Datastudio at the very bottom

chart>bar graph parameter settings:

parameter:

Control>input box settings when this number changes, the ref bar shows at that number which I call our "goal" I want this to work with the drop-down menu called Bay:

Answer 1

If you want to create a list of choice, you can do that if you create a parameter using the list of values and you enter your list of value manually.

If you want to use a list from a datasource and use that selection a the value for the reference line that is not possible within the same chart. You could create another chart, line chart, and show that line in front of your bar chart. But that would require that your Y axis are identical. You won't be able to use the auto Y axis, otherwise your 2 graph might end up with 2 different scale and your ref line will not represent the proper number on the bar chart. To do this

• you need a datasource with your goalline to be a different datasource than the one used in the bar chart. This datasource will contains two rows for each goaline value in the first column. the second column will contain the metric for the line. We want zero and a 1. for example

goaline	horValue
23.5	0
23.5	1
29	0
29	1

You will be filtering your result when you select within the drop down

• you need to create your control dropdown list with your goalline datasource and choose goaline for your dimension
  • make that dropdown single select
• create your bar chart and set the min and max for your Y axis
• copy your chart and transform it into a line chart
• for your dimension, choose horValue
• for your metric create field choose goaline
• in the style of the chart, for your series #1
  • for the line thickness choose "none"
  • hide data label
  • add a reference line
  • select type metric and choose goaline,calculation max
  • remove the legend
  • untick show axes and resize the chart area with your mouse in order to make sure the graph aligns with the one underneath the problem with this is that you reference line will block your chart below. If wanted to use the data label card or total card the chart in front will prevent you to use it. You could have your ref chart behind the graph, the ref line chart will be slightly hidden from the bar chart in front. Otherwise you could also use the area chart behind the bar chart to create the same thing, but I will let you figure this one out

I forgot to turn off the label of the ref line graph.