Search code examples
tableau-apitableau-desktop

Tableau - Dynamic Reference line that changes based filter selection

Instead of repeating the entire question, this is exactly what I'm trying to accomplish:

https://community.tableau.com/s/question/0D54T00000VBxdgSAD/dynamic-reference-line-that-changes-based-filter-selection

I have two data sources.

One of which creates a visualization along with filter of the following values:

foo
bar
baz

and another which contains the value I want the reference line to be set at based on the filter selection:

selection value
foo        10
bar        20
baz        50

Where a filter selection of foo would set a reference line at 10, bar at 20, baz at 50, foo and bar at 30, foo and baz at 60, bar and baz at 70, etc.

Is there some combination of parameters or calculated fields I can use to show the appropriate reference line value from data source 2 based on the filter selection from data source 1?

Edit: Grammar

Solution

  • Two options:

    1. You can point a reference line to a calculated field.

    2. You can create a dual axis viz with bars and a dynamic line chart.

    Using a Calculated Field as a Reference Line

    First, map your values. We'll use this for a dual axis chart as well.

    Create a calculated field [ref_mapping] that will map the values you need:

    CASE [director]
WHEN 'foo' THEN 10 //if your [value] column already has the number you need ***AND is already mapped to your directors***, you can replace 10 with [value]
WHEN 'bar' THEN 20 //[value]
...
END

    Second, make your values dynamically change based off the view filter.

    Next take a windowed sum of [ref_mapping] so it only calculates based off what the view is filtered to (This assumes [director] is in the filter pane).

    Make a new calculated field called [dynamic_ref]:

    WINDOW_SUM([ref_mapping], FIRST(), LAST())

    Now go to analytics pane and add a reference line by dragging it to your viz. Edit the reference line by selecting the value from drop down. The field [dynamic_ref] should now available to use as reference line.

    Create a Dual Axis Bar Chart

    • Bring the two measure you wish to compare to the rows shelf(say SUM([Sales]) and SUM([ref_mapping])).
    • Right click one of your measure pills and select dual axis.
    • In your Marks Pane make sure Bar is selected for the data you are comparing the reference value against (such as SUM([Sales])) and Line is selected for the actual reference values (such as SUM([ref_mapping]).

    Helpful Links