4 Replies Latest reply on Jul 27, 2018 1:42 PM by Kelsey Barth

    Adjustable Reference Line Based on Filters

    Kelsey Barth

      Hi everyone,

       

      I am trying to create a reference line that changes depending on filter selection.

       

      In the attached workbook, I created the 'Target' Calculated field using a Case When and IF statements to define values which change based on which Region is selected. My intention is for the ELSE section of the Case When statement to be the values assigned when (All) is selected in the Region filter. These values are the result of a weighted average that I calculated outside of Tableau.

       

      I dragged Target to the Marks Detail as a measure (MIN), then right clicked on the y-axis to add a reference line. I chose the Target field for the value and selected minimum.

       

      This works how I would like it to when an individual region is selected, but not for (All). Since I am using MIN(Target), it is taking the minimum of the four individual regions instead of using the value in the ELSE section that I had intended, but I don't think any of the other Measure options will work either since they all aggregate and I just want the value defined in my calculated field. I have also tried changing Target to an attribute or dimension with no luck.

       

      There are a lot of factors impacting this, so I am not sure where to go next and am not familiar enough with how Tableau works in general. Is the issue my calculation, can I somehow dis-aggregate this Calculated Measure, do I need to use a parameter, or am i just going about this the wrong way?

       

      I have attached a workbook using the Superstore Data that replicates what I am trying to achieve.

       

      Thank you in advance, I appreciate your time.

        • 1. Re: Adjustable Reference Line Based on Filters
          swaroop.gantela

          Kelsey,

           

          I am not sure what the downstream ramifications of this may be,

          but for the purposes of the getting the All Target lines,

          you could try instead a calculated field of :

           

          IF WINDOW_MAX(MAX([Region]))<>WINDOW_MIN(MIN([Region])) THEN

              IF ATTR([Sub-Category]) = 'Bookcases' THEN 200

              ELSEIF ATTR([Sub-Category]) = 'Chairs' THEN 400

              ELSEIF ATTR([Sub-Category]) = 'Furnishings' THEN 600

              ELSEIF ATTR([Sub-Category]) = 'Tables' THEN 300

              END

          ELSEIF ATTR([Region])='Central' THEN

              IF ATTR([Sub-Category]) = 'Bookcases' THEN 30

          ...

           

          What the top part is doing is saying that in the case when All is selected,

          the max region and the min region won't be the same, so it should return

          the prescribed value.

           

          Please see the workbook v10.4 attached in the Forum thread.

           

          2766943target.png

          1 of 1 people found this helpful
          • 2. Re: Adjustable Reference Line Based on Filters
            Kelsey Barth

            This is doing exactly what I need. I am going to look more into Window functions so I can understand it better.

             

            Do you have concerns about the downstream ramifications? Or just unsure?

             

            Thank you so much!

            • 3. Re: Adjustable Reference Line Based on Filters
              swaroop.gantela

              Kelsey,

               

              Glad it worked for you.

               

              Regarding the calculation, you can see a bit of how it works on Sheet 3.

              My understanding of it is that we want to look over the entire sheet if

              the maximum Region is different from the minimum Region, which is

              our indicator that All has been selected.

              The use of WINDOW_MAX here is to specify that we want to look down

              the entire sheet. If we had just used MAX(Region)<>MIN(Region), it

              would have done the comparison on each individual row, and would

              have failed because each row only has one Region.

               

              It looks like there are other ways to determine if All has been selected:

              Customizing a Calculation When "All" is Selected in a Filter | Tableau Software

               

              With regards to downstream effects, I wasn't sure if your true workbook

              had other things going on that sheet, or if it was pretty much similar to the example

              you had shared. If it is like the example, then I think it should be OK.

               

              All the best.

               

              276943target2.png

              • 4. Re: Adjustable Reference Line Based on Filters
                Kelsey Barth

                Luckily the true workbook was very similar so there were no other issues.

                 

                This explanation and extra was really helpful too. Thanks again!