KNOWLEDGE BASE

Unable to Create Reference Lines for Individual Dimension Members in the View


Published: 27 Sep 2016
Last Modified Date: 04 Sep 2017

Issue

When adding a reference line to a view that displays multiple dimension members from a single dimension, you are unable to reference a single.

Environment

Tableau Desktop

Resolution

You can show reference lines for individual dimension members in the view by splitting the values into two separate calculated fields using the steps below. Please note that these steps can also be reviewed in the attached Reference Lines - Dimension Members.twbx using the Superstore Sample data source. 
 

Step 1: Create Calculated Fields

Create one calculated field per reference line. In this example, we will create a calculated field for each "Category" dimension member.
  1. Select Analysis > Create calculated field...
  2. Name the calculated field "!Furniture Avg Sales"; Enter the following formula and click OK: 
    IF ATTR([Category])="Furniture" THEN WINDOW_AVG(SUM([Sales])) END
  3. Select Analysis > Create calculated field...
  4. Name the calculated field "!Office Supplies Avg Sales"; Enter the following formula and click OK: 
    IF ATTR([Category])="Office Supplies" THEN WINDOW_AVG(SUM([Sales])) END
  5. Select Analysis > Create calculated field...
  6. Name the calculated field "!Technology Avg Sales"; Enter the following formula and click OK: 
    IF ATTR([Category])="Technology" THEN WINDOW_AVG(SUM([Sales])) END
 

Step 2 - Build the View

  1. Drag [Order Date] from to Columns.
  2. Right-click [YEAR(Order Date)] and set the aggregation to continuous MONTH.
  3. Drag another instance of [Order Date] to the Filter shelf and filter the year to only show 2014 and 2015.
  4. Drag [Sales] from to Rows
  5. Drag [Category] to Color.
  6. Drag "!Furniture Avg Sales", "!Office Supplies Avg Sales", and "!Technology Avg Sales" to Detail on the Marks card. (This will allow us to reference these values in the reference lines)
  7. Select the Analytics tab > Reference Line. Drop the line on SUM(Sales) for the Table.
  8. In the Reference Line menu, select "!Furniture Avg Sales" from the dropdown menu for Value.
  9. Set the aggregation to Average.
  10. Set the Label to Custom, type "Furniture" and click OK.
  11. Repeat from step 7  for "Office Supplies" and "Technology".

Cause

Tableau Desktop views the dimension as a single field, not by its individual dimension members.
Did this article resolve the issue?