3 Replies Latest reply on Oct 25, 2016 11:10 AM by Simon Runc

    Pareto Drop Line

    Vincent Baumel

      I have a Pareto chart in which my first x-axis is made up of products sorted by sales, and the second is a %-of-total based on the running sum of sales. I've got the threshold set to a parameter so that the user can color and populate the top n% of items, but I'd like to add a vertical drop line where the parameter intersects the % line. Something like this:

       

       

      Would this be possible? Tableau doesn't seem to want me to set a vertical drop line when the x-axis isn't continuous. I've tried to figure it out with using a rank() calc, but I can't quite seem to get it right (plus my line then becomes a series of points). Any help would be great!

       

       

      edit: initially attached a 10.1 workbook. Have replaced it with 10.0 version.

        • 1. Re: Pareto Drop Line
          Simon Runc

          hi Vincent,

           

          ...quite a fun little challenge this one! (...and a good job I'm on the beta trial, so I could open your workbook!!)

           

          So here is one way to do it...yes Tableau won't let you create reference lines on discrete pills (as blue pills create headers and green pills create axis), so there is no axis in which to create a reference line!!

           

          So first is to get the axis to be continuous. For this, I've used index() and set it to run across [Product Name] (which I've brought into the "Detail" tile). I also now need to change the pareto table calc to run over [Product Name], as the pill arrangement has changed, I can no long use the Table (across) and need to specify the dimension I want to run things over.

           

          Once we have this I set up the following calculation for the drop line

           

          IF (RUNNING_SUM(SUM([Sales])) / TOTAL(SUM([Sales])))>=([Pareto Threshold]/100)

          THEN [index]-0.5 END

           

          I then bring this onto the detail shelf (on the index() axis), and set this to compute-using [Product Name]. I add a reference line for this field, and set it to Table scope and MIN (as per the below image)

           

           

          the -0.5 is just so it falls between 2 products.

           

          Now it doesn't perfectly line up with the pareto, but would get pretty complicated (more than it is already!!) to work out exactly where between the products it would go...

           

           

          ...it's possible, but probably not worth the effort (and with the dimension not "really" being continuous doesn't make much sense)

           

          Hope that does the trick, and makes sense, but please let me know if not.

          2 of 2 people found this helpful
          • 2. Re: Pareto Drop Line
            Vincent Baumel

            Thanks, Simon! Wow, great direction on this. I found a way to get the intersection almost exact, and it's actually not complicated at all! If you change your SUM(Pareto Reference Line) from Average to Minimum then it lines up just about right. The coloring is still correct, and in states where there's a enough products to render the bar graph similar to Area marks rather than Bar, it looks great. I love it!

            2 of 2 people found this helpful
            • 3. Re: Pareto Drop Line
              Simon Runc

              Nifty!!...I was thinking about trying to calculate at which point between Product 4 and 5 (if 4 to 5 is the boundary) the Pareto would fall (so 4.1234, or 4.2345....). Nice solution looking at it the other way!

               

              You also might find this useful...the axis here is continuous (although in reality are integers, as you can't have half a company!), and uses a similar technique (you're lucky I watched the video for this over the weekend, or I wouldn't have had a clue).

               

              Tableau Tip Tuesday: How to Create a Two Color Pareto Area Chart