Reference lines are essential for showing the average lines per pane or table. Using the Tableau Parameter, you can show or hide Reference lines, making them dynamic. This Tableau article shows how to use the parameter, calculated field with If else conditions to dynamically show or hide Reference lines.
To demonstrate the usage of Tableau Parameter to Show or Hide Reference Lines, we used the Sample Superstore Excel file. Please visit the Excel Source article to understand the Data Source. We need a parameter for Show or Hide options and a calculated field to write the report logic to achieve this.
Tableau Parameter to Show or Hide Reference Lines
Drag and drop the Category and Sub-Category dimensions to the Columns shelf and Sales Measure to the Rows shelf to create a Side-by-Side Bar Chart. Please add the Category dimension to the Color shelf to add color to each section. Next, click the T button in the toolbar to add the Data labels. For the remaining charts in Tableau, please click here.
Right-click on the Sales Axis and choose the Add Reference Line option from the context menu to open the following window. Please change the scope to Per pane, Label as Value, and format the line to green color. It will find the sum of the sales average of each Category and, using the average, show the reference dotted line for each Category. For more information on adding reference lines>> Click Here!
In this Tableau example, we must create a parameter Show or Hide Reference Lines. So, click the marked down arrow and choose the Create Parameter option from the menu shown. Please use this window to add options. First, assign Choose Option as the parameter name, change the Data type to string, set the Allowable values to List, and add Show and Hide values.
Right-click on the newly created Choose Option parameter and select the Show Parameter option. Next, to utilize the parameter, we must write a logic. So, go to the Analysis Menu and select the Create Calculated Field.. option.
The following Calculated Field expression uses the IF ELSE condition to check whether the parameter value is Show or Hide. If the text = show, calculate the Window average of the Sum of Sales. Otherwise, return NULL.
IF [Choose Option] = "Show"
THEN WINDOW_AVG(SUM([Sales]))
ELSE NULL
END
First, drag and drop the Average calculated field to the Details shelf and click the down arrow to edit the table calculation. Next, change the Compute Using option from default Table(Down) to Category.
Instead of using the average of SUM(Sales) as the reference line value, we use this Tableau calculated field with parameter value to show or hide them. To do so, right-click the Sales Axis and choose the Edit Reference Line option. Please change the Line value to the newly created calculated field.
TIP: Add this calculated field to the Marks shelf (Details section). Otherwise, you can’t select it from the Reference Line window.
As you can see, there is no reference line because we have chosen the Hide option.
The reference lines will display per pane if we change it to the Show option. Using a similar approach, please experiment with different windows calculations and reference line options.
