2 Replies Latest reply on Feb 11, 2016 3:11 PM by Josh Craig

    Adding reference lines to aggregated panes

    Josh Craig

      I'm trying to add reference lines to a chart with three dimensions on columns (test, state, region) and one measure on rows (score).

       

      I want a reference line that shows the 25th percentile of scores by test (but not broken down by state or region).

       

      This is easy enough to do with two dimensions (e.g. test, state) because I can just add PCT25(Score) to Detail and create a reference line per pane.

       

      But when I add the third dimension on columns (region) if I select reference lines by pane I get a different line for each state rather than for each test.

       

      Is there a way to make Tableau treat the first dimension (test) as the panes? (Or solve my problem some other way?)

        • 1. Re: Adding reference lines to aggregated panes
          Adam Lopuch

          I'm not sure about the 25th percentile piece... if you have some way to calculate that manually, then you can use Level of Detail expressions on Tableau 9+ to get your reference lines to apply the calculation only on the [test] dimension.

           

          I did something similar, although on an easier-to-aggregate measure (average); I'm not sure how (and if) it might work when calculating a percentile value. 

           

          Example:

          * Assuming viz has dimensions [test], [state], [region]

          * Assuming some magic function PCT25([score]) that will generate 25th percentile

           

          1. Create new measure [25th Percentile Excl State Region]:  { EXCLUDE [state], [region] : PCT25([score]) }

          * note the curly braces before/after to signify it's an LOD function

          * remember: Level of Detail expressions are on Tableau 9 or higher

           

          2. Add the new measure to the Detail shelf

           

          3. Use the new measure as the reference line, per pane

          * whether it aggregates as SUM() or AGGR(), use something like 'Average' to get a consistent value

          • 2. Re: Adding reference lines to aggregated panes
            Josh Craig

            Hi Adam

             

            Thanks for the answer. This is not quite working but I think it's definitely on the right track.

             

            I think part of the problem is something to do with aggregation and I didn't explain myself clearly. I wasn't using a magic function called PCT25() in a calculated field. PCT25() is actually the aggregation I'm using for [score] (i.e. percentile>25). (I just clicked 'edit shelf' and it changes to just be PERCENTILE([score],0.25).)

             

            I tried doing the calculated field as you suggest: {EXCLUDE [state],[region]:PERCENTILE([score],0.25)} but it seems to return more than one 25th percentile value per test because when I add the calculated field to Detail using the SUM() aggregation the reference line moves around depending on whether I choose the aggregation to be minimum, maximum, average, etc.

             

            Is there a way to set it up so it just calculates a single value for the 25th percentile for each test?

             

            I greatly appreciate your help!!