In an internal Tableau class I was teaching earlier this week, I reviewed how to create a Reference line using a Target or Sales Goal Parameter. But what if you wanted to have separate targets or goals for each Category?
Single Reference Line Across a Table
I had a simple bar chart, as shown below with Category and Sub-Category, displaying the Sales for each.
I created the below parameter with a target of $150,000.
Then I created a calculated field referring to this parameter to display any Sub-Categories that did not make the target amount.
Input Target Color
SUM([Sales]) > [Input Target]
I placed this calculated field on the Color marks card of my bar chart. I changed the color to grey for any Sub-Categories that made the target and red for any that were under.
I then added a Reference line from the Analytics tab to the dashboard and selected the Input Target parameter for the line.
The final view appeared as below;
The purpose of this chart was to display the parameter and show that the colors change as the user enters a new value into the parameter.
Multiple Reference Lines
For this method, you would create three different parameters for each Category. Here is the example of the Furniture Target parameter.
You can right-click on this parameter to duplicate it and rename it Office Supplies.
Then duplicate it again for Technology.
Once that is complete, display all three Parameters. (Right-click on them and select Show Parameter.)
Create the following calculated field for the reference line;
Category Reference Line
IF [Category] = "Furniture" then [Furniture Target]
ELSEIF [Category] = "Office Supplies" then [Office Supplies Target]
ELSEIF [Category] = "Technology" then [Technology Target]
END
Place this field on the Detail marks card of the worksheet.
Change the aggregation of this field to Minimum.
Then on the Analytics tab, drag Reference line to the canvas and place it on the Pane.
On the Edit Reference Line, Band or Box pop-up menu, configure it as shown below;
You can right-click on the reference line in the worksheet to format it, in order to display the numbers as a currency without decimal places.
Now you will need to create the calculated field to add colors to the bars;
Target Color
SUM([Sales]) > MIN([Category Reference Line])
Place this calculated field to the Color marks card.
Change the True value to grey and the False to red.
Your chart will appear as below.
I changed the Furniture Target to $100,000, Technology to $170,000 and Office Supplies to $60,000. I have this chart at the bottom of my Parameter dashboard in Tableau Public here. Feel free to check it out!
Comments