This blog post will examine adjustable reference lines in Tableau. While there are many ways to add reference lines in Tableau, using a parameter to control the reference line provides a great way for end users to interact with your dashboard by adding a degree of flexibility. For instance, a reference line might help show users what happens before or after a particular date they input or might enable your colleagues in regional offices see whether their shifting sales targets have been met.
For the purpose of this blog post, I’m using Sample – Superstore data available in Tableau. I’m going to look at creating a reference line to see whether or not a sales target has been met.
Here’s our initial view:
Go ahead and place the ‘Sales’ measure on the columns shelf and ‘Product Sub-Category’ on the rows shelf.
Step 1: Create a Parameter
- In the Data pane, click the drop-down arrow in the upper right corner and select ‘Create Parameter’.
In the Create Parameter dialog box, you will see the following properties:
- Name: This parameter will appear as new field to the bottom left hand side of the data pane and will therefore require a name ( I’ve gone with ‘Beats 100k Sales Target’).
- Data type: In Tableau, Numerical values can be either integers or An integer (more commonly called an int) is a number without a decimal point. A float is a floating-point number, which means it is a number that has a decimal place. Floats are used when more precision is needed. Given we are looking at sales and I want the figures to be precise, I’m going to go ahead and select ‘float’.
- Current Value: This is the value that your parameter will show by default on your view. As I want to know which products have hit the sales target of $100k or over, I am going to set that as my current value.
- Display Format: Here you can select the number format of your parameter, such as number, currency or percentage.
- Allowable Values: There are three options here that your parameter will display: all, list or range. The ‘All’ option will allow the user to enter any value of their choice; the ‘List’ option allows you to select a predetermined set of values you want the user to be able to select from and must correlated to a data field in your view but the Display as can be anything.The Range option is available for float, integer, date as well as date and time. Imagine your parameter control as a number line. Range allows you to define the starting point of your line and the step size allows the user to jump in increments (in this case of $10k) on the parameter control. I am going to set the minimum value as ‘0’ and the maximum value as $350,000. If I set the minimum value to 1, then the reference line would start at $100,001, which may be necessary if the sale target was anything over $100k, but not $100k itself.
It’s always good to play around with setting this at different values and seeing how the graph updates accordingly. Select OK.
Step 2: Show Parameter Control
- Right click on the parameter on the bottom left of your screen and select ‘Show parameter control’. This will now appear on the top right hand side of the workbook as a dropdown. However you’ll notice this doesn’t actually do anything as we haven’t linked it up yet.
Step 3: Add the Reference Line
Go to the Analytics Pane, to the left of the Data pane, select the reference line and drag it onto the graph. A small box should pop up. Select the ‘Table’ option, as below.
The Reference Line dialog box will now open:
• Line Value: On your value line drop down menu select your parameter.
• Label: Leave as is.
• ToolTip: Leave as is.
• Formatting Line: Amend as per your personal preference.
Hit OK and you’ll get your reference line which can now be dynamically adjusted. Play around with the parameter control and see it update accordingly.
Step 4: Adjusting the Colours on either side of the Reference Line
Colouring either side of your reference line can be a powerful tool in your visualisation kit. In this case, it enables users to distinguish even more clearly between the products that beat vs those that didn’t beat the $100k sales target. In order to have this dynamically update when we change our parameter control, we need to create a calculated field and set it as follows:
This logical calculation allows us to determine if a certain condition is true or false (i.e. if the sum of sales is greater than the parameter). If its true, it will return one result, if it’s false, it will return another.
Click OK on the calculation and then drop it on the colour marks. Doing so will assign a colour to the results that are true (i.e. when the sum of sales is greater than the parameter (orange)) and another colour to the results that come back false (i.e when the sum of sales is not greater than the parameter (blue)). Adjust the colours to your preference and then update the parameter control to see your graph dynamically update.