Reference lines in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a sales dashboard that shows monthly sales with clear markers for average sales and sales targets to quickly identify performance.

📊 Data: You have monthly sales data for the current year, including Month, Sales Amount, and Sales Target columns.

🎯 Deliverable: Create a line chart showing monthly sales with reference lines for average sales and sales target.

Sample Data

Month	Sales Amount	Sales Target
January	12000	15000
February	14000	15000
March	13000	15000
April	16000	15000
May	17000	15000
June	15000	15000
July	18000	15000
August	19000	15000
September	20000	15000
October	21000	15000
November	22000	15000
December	23000	15000

1

Step 1: Connect your Tableau workbook to the sales data source containing Month, Sales Amount, and Sales Target.

No formula needed; just connect the data source.

Expected Result

Data source is connected and visible in Tableau.

2

Step 2: Create a line chart with Month on the Columns shelf and SUM(Sales Amount) on the Rows shelf.

Drag 'Month' to Columns, drag 'Sales Amount' to Rows, and set aggregation to SUM.

Expected Result

Line chart showing total sales for each month.

3

Step 3: Add a reference line for average sales across all months.

Right-click on the Y-axis, select 'Add Reference Line', choose 'Per Table', set value to 'Average' of SUM(Sales Amount), label it 'Average Sales', and set line style to dashed.

Expected Result

A dashed horizontal line appears at the average sales value with label 'Average Sales'.

4

Step 4: Add a reference line for the sales target value.

Right-click on the Y-axis, select 'Add Reference Line', choose 'Per Table', set value to a constant 15000 (the sales target), label it 'Sales Target', and set line style to solid red.

Expected Result

A solid red horizontal line appears at 15000 with label 'Sales Target'.

5

Step 5: Format the chart for clarity: add axis titles, adjust colors, and ensure labels are readable.

Set Y-axis title to 'Sales Amount', X-axis title to 'Month', use distinct colors for the line and reference lines, and increase font size for labels.

Expected Result

Clear, readable line chart with labeled reference lines and axis titles.

Final Result

Month ->

 25000 |                          *
       |                         *
 20000 |                    *    *    <--- Sales Amount line
       |                   *     *
 17500 |-------------------|-----|-------------------  <--- Average Sales (dashed line)
 15000 |===================|=====|-------------------  <--- Sales Target (solid red line)
       |                   |     |
 13000 |           *       |     |
       |          *        |     |
 12000 |     *             |     |
       | Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Legend:
* = Monthly Sales
--- = Average Sales (dashed)
=== = Sales Target (solid red)

✓Monthly sales generally increase over the year.

✓Sales exceeded the target from April onwards.

✓Average sales line helps quickly see which months are above or below average.

Bonus Challenge

Add dynamic reference bands showing sales performance zones: below target, between target and average, and above average.

Show Hint

Use 'Add Reference Band' feature in Tableau with ranges set between sales values and customize colors for each band.

Practice

1. What is the main purpose of adding a Reference Line in a Tableau chart?

easy

A. To highlight a specific value like an average or target on the chart

B. To change the color of the chart background

C. To filter out unwanted data points from the view

D. To add a new data source to the workbook

Solution

1. Step 1: Understand the role of reference lines

   Reference lines are used to mark important values such as averages, targets, or constants on a chart to help viewers compare data easily.

2. Step 2: Identify the correct purpose

   Among the options, only highlighting a specific value like an average or target matches the purpose of reference lines.

3. Final Answer:

   To highlight a specific value like an average or target on the chart -> Option A

4. Quick Check:

   Reference lines = highlight key values [OK]

Hint: Reference lines mark important values on charts [OK]

Common Mistakes:

• Confusing reference lines with filters
• Thinking reference lines change chart colors
• Assuming reference lines add data sources

2. Which of the following is the correct way to add a reference line in Tableau?

easy

A. Drag a measure to the Filters shelf

B. Right-click on the axis and select 'Add Reference Line...'

C. Double-click on the data source name

D. Click 'New Worksheet' and type the reference value

Solution

1. Step 1: Recall how to add reference lines

   In Tableau, reference lines are added by right-clicking on an axis and choosing the 'Add Reference Line...' option.

2. Step 2: Eliminate incorrect options

   Dragging a measure to Filters filters data, double-clicking data source name does nothing related, and creating a new worksheet doesn't add reference lines.

3. Final Answer:

   Right-click on the axis and select 'Add Reference Line...' -> Option B

4. Quick Check:

   Add reference line = right-click axis [OK]

Hint: Right-click axis to add reference line quickly [OK]

Common Mistakes:

• Trying to add reference lines via Filters shelf
• Looking for reference line option in data source menu
• Confusing worksheet creation with adding lines

3. Given a sales bar chart, you add a reference line set to the average sales. What will the reference line show?

medium

A. A line at the average sales value across all data points

B. A line at the maximum sales value

C. A line at the total sum of all sales

D. A line at zero sales

Solution

1. Step 1: Understand the average reference line

   When you add a reference line set to average, Tableau calculates the mean value of the sales data and draws a line at that value.

2. Step 2: Match the correct description

   The average is not the total sum, maximum, or zero, so the line will be at the average sales value.

3. Final Answer:

   A line at the average sales value across all data points -> Option A

4. Quick Check:

   Average reference line = average value [OK]

Hint: Average reference line marks mean value [OK]

Common Mistakes:

• Confusing average with total sum
• Thinking average line shows max value
• Assuming reference line defaults to zero

4. You added a reference line at a constant value of 100, but it does not appear on your chart. What is the most likely reason?

medium

A. Reference lines only work on pie charts

B. Reference lines cannot be constants in Tableau

C. You forgot to refresh the data source

D. The constant value is outside the axis range displayed

Solution

1. Step 1: Check axis range relevance

   If the constant reference line value is outside the visible axis range, Tableau will not display it on the chart.

2. Step 2: Verify other options

   Reference lines can be constants, refreshing data is unrelated, and reference lines work on many chart types, not just pie charts.

3. Final Answer:

   The constant value is outside the axis range displayed -> Option D

4. Quick Check:

   Reference line visibility depends on axis range [OK]

Hint: Check axis range if reference line is missing [OK]

Common Mistakes:

• Believing constants can't be reference lines
• Assuming data refresh fixes reference line display
• Thinking reference lines only apply to pie charts

5. You want to add a reference line that shows the median sales per region on a sales by region bar chart. Which steps should you follow?

hard

A. Drag the sales measure to Filters and select median aggregation

B. Create a calculated field for median sales and add it as a color mark

C. Right-click the sales axis, choose 'Add Reference Line', set scope to 'Per Pane', and aggregation to 'Median'

D. Add a reference line with aggregation set to 'Sum' and scope 'Entire Table'

Solution

1. Step 1: Add reference line on sales axis

   Right-click the sales axis to access the reference line options.

2. Step 2: Configure reference line for median per region

   Set the scope to 'Per Pane' (which corresponds to per region in this chart) and aggregation to 'Median' to show median sales per region.

3. Final Answer:

   Right-click the sales axis, choose 'Add Reference Line', set scope to 'Per Pane', and aggregation to 'Median' -> Option C

4. Quick Check:

   Median per region = reference line with 'Per Pane' and 'Median' [OK]

Hint: Use 'Per Pane' scope and 'Median' aggregation for per-region median [OK]

Common Mistakes:

• Using Filters instead of reference lines for median
• Confusing sum aggregation with median
• Setting scope to entire table instead of per pane