1 2 Previous Next 20 Replies Latest reply on May 29, 2018 12:54 AM by v k

    Educational Brain Teaser: Scatter Plot - detail & percentile indicator

    Simon Runc

      ...It's brain teaser time again!!

       

      Last week, I got a question on creating a scatter plot, which plots 2 measures (X Axis & Y Axis) at the Row Level of detail, and on the same plot also show a 'mark' for the 85th Percentile of both measures. Image of final desired result below (the Reference Lines weren't requested, but I've included them here so you can see the Red mark is the 85th Percentile of both measures)

       

      Scatter Plot at Different Levels.JPG

       

      This could well be me , and you all come back with a solution in 2 minutes with a '1 for difficulty' score, but this proved slightly more complicated than I first thought!!

       

      Usual rules apply....A starter workbook is attached (8.2). I'll add the solution I came up with next week, although if it drives you as crazy as it drove me, you can see what I came up with here All Values and Percentile together.


      Please add a comment below (without spoilers, please) with how difficult you found this, ranking from 1 (easy) to 5 (extremely difficult or impossible).

       

        • 1. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
          Simon Runc

          Thanks Shawn. Good to know it wasn't just me!! When I eventually stumbled (and I mean 'stumbled' on the solution!) it took me a further few days to understand why the 'approach-I-thought-would-work' didn't!

           

          As Jonathan Drummey often points out (and I equally often forget!)...they key to understanding what's going on is to look at the number of marks...

          • 2. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
            Yuriy Fal

            Hi Simon,

             

            Thank you for the teaser.

            Give it 2.5 (got success from my second guess :-)

             

            Yours,

            Yuri

            1 of 1 people found this helpful
            • 3. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
              Mark Jackson

              I got it to work quickly, but only when starting from the axis that had a value plotted at the 85th percentile. I gave up plotting the point when you don’t have a value that falls exactly onto the 85th percentile.

              1 of 1 people found this helpful
              • 4. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
                Jonathan Drummey

                I give it a 5 in v8.2, I couldn't do it. Your solution was great, Simon!

                 

                This is a problem that has had me stumped for years when trying to do it solely in Tableau. Trying to get an "extra" mark to show differently from the rest actually helped lead to the insights on Creating a Dynamic “Parameter” with a Tableau Data Blend | Drawing with Numbers. I mention that because it shows how solving a problem in one direction can lead to helpful unanticipated results (and how my brain can get stuck on something and fail to consider alternative routes).

                 

                I did come up with a v9 solution that is more complicated, on the other hand it allows for an arbitrary number of additional marks and avoids the problem that Simon described in his original post.

                 

                2015-06-06 09_52_27-Tableau - N+1 Marks with v9.png

                The basic "trick" used to build this is extensible to a number of other problems in Tableau...I see some blog posts coming...

                 

                Jonathan

                1 of 1 people found this helpful
                • 5. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
                  Simon Runc

                  Thanks Jonathan...that is a brilliant solution (utilizing nearly all the new T9 functionality! ), and as you say allows for as many of these 'extra marks' as desired...I hadn't even considered this method.

                   

                  As I alluded to in my reply to Shawn, I (kind of) stumbled across my solution, and took a further couple of days for the understanding of 'why is Tableau doing that?' to sink in...Look forward to the Blog(s)!

                   

                  This is a great example of why helping on the community is so valuable...'I came to solve a, seemingly, simple question...and left with a much deeper understanding of the software'!

                  • 6. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
                    Simon Runc

                    …So I hope everyone has enjoyed this little challenge...below is the solution and some comments on the problem.

                     

                    Problems:

                    The problem many of you may have experienced here, I’m guessing (as I had the same problem when I first looked at this question!), is that Tableau exhibits some ‘unexpected’ behavior when using a ‘double’ continuous axis (i.e. 2 continuous Axis on both the Columns X, and Rows Y Axis).

                     

                    If you tried to approach this, as I did initially, you probably ran into one of 2 problems. One when you tried to set up the Axis as below…By Plotting SUM([Total]), and SUM([Mean]), detailed by RecordID, and then Plotting the PERCENTILE([Total], 0.85), and PERCENTILE([Mean], 0.85), but not detailed by RecordID….We get an extra mark on the Total Axis, which is plotted at the SUM of all Totals Position.

                    Wrong Solution 1.JPG

                    Or by using a Table Calcs for your percentile values, WINDOW_PERCENTILE(SUM([Mean]),0.85) & WINDOW_PERCENTILE(SUM([Total]),0.85) On the secondary axis. This time plotting both with the RecordID detail, but ‘Computing’ the Table Calcs over RecordID.

                     

                    In this one, although the 2 Percentiles are correct (if you look at the tooltip, you can see all the points have the same, single, 85th Percentile), it actually plots a mark for 85th Percentile Mean (at every SUM([Total position]), and vice-versa for Total.

                    Wrong Solution 2.JPG

                    So where we have 2 different continuous Axis measures, in this case SUM([Total]) and PERCENTILE([Total], 0.85) & SUM([Mean]) and PERCENTILE([Mean], 0.85), There is a mark created for each Percentile Total, at each SUM([Mean]) position (looking at the Table Calc version).

                     

                    Solution:

                    …So how do we get round this, in fact quite simply! The key is being able to use the same continuous Axis measure for both.

                     

                    So firstly I created my percentile continuations for the Mean and the Total

                     

                    PERCENTILE([Mean], 0.85)

                    PERCENTILE([Total], 0.85)

                    I then brought these fields onto the Rows and Column Shelves, and brought my RowID into the detail tile. This is the trick!...the PERCENTILE for a single value, is the single value, so by detailing it by RowID has the same effect as bringing it in as a SUM. I then duplicated both these fields (I use the shortcut of holding CTRL and dragging the column to the right of the existing column). This brings up the same view 4 times.


                    Simple Solution - Set Up 1.JPG

                    In the second view (i.e. the one I just duplicated) I then removed the RowID from the Level of Detail shelf, which then gives the 85th Percentile for all the data.

                    Simple Solution - Set Up 2.JPG

                    Next I dual axis both Row and shelf (and Synced Axis), and then changed the colour on each level, and we have the desired Viz!

                     

                    There is a bit of visual-trickery here, if you look at the bottom right of the screen you'll see we have 114 marks, although it looks like 57 (the 56 Rows and the 1 overall level), this is because the 56 marks are marked out twice, and the 85th Percentile twice, but within a dual-axis they plot on top of one another. It’s always very useful to look at the number of marks to help you understand what is going on (if you look at the 'failed' table calc attempt this shows 224 marks, so I know Tableau is plotting all 56 #4 times).

                     

                    I would also point you in the direction of Jonathan’s excellent solution using Tableau 9.0 (employing all the new functionality at our disposal, by first Pivoting the data to create a ‘real’ dimension on the measure names, and then using LoD calculations).

                     

                    http://community.tableau.com/message/379437#379437

                     

                    Jonathan’s solution also has the advantage of being able to plot as many of these percentile points as you like (and this general methodology can  be expanded to solve many other problems).


                    The attached solution is built in version 8.3

                    • 7. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
                      Bill Lyons

                      Thank you Simon! This had me stumped. I struggled with it for hours. Excellent tip: the percentile of a single value is a single value! The same could hold true for most types of aggregations.

                       

                      I'm checking Jonathan's solution next. I look forward to it too!

                      1 of 1 people found this helpful
                      • 8. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
                        Simon Runc

                        Thanks Bill...I only posted it as I struggled with the Original question in the same way (especially having given the OP the old '...ah yes we can solve this, what version of Tableau are you on?...'), before kind of stumbling on the answer, and realized this was definitely a 'Brain Teaser in waiting'!!

                        • 9. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
                          v k

                          Hi,

                           

                          I have a similar requirement.

                           

                          I have a risk/return scatter plot (risk on x axis) & (return on y axis), Account id in detail. I want to add a data point(constant)  madeup of (y co-ordinate - benchmark return) and  (x co- ordinate benchmark risk). Something very similar to what you have Simon, but mine is a constant not a percentile which is used to compare return/risk of each account with benchmark return/risk.

                          So the data is something like this

                           

                          Acctid Return Risk Benchmark Return   Benchmark Risk

                          A1          3        2        5                                 8

                          A2          4        3        5                                 8

                          A3          6        6        5                                 8

                          A4          8        9        5                                 8

                          A5          10     12       5                                 8

                           

                          Can anyone help me with this?

                           

                          Thanks in anticipation!

                           

                          Regards,

                          Varun

                          • 11. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
                            v k

                            Hey,

                             

                            Thanks for the reply Simon.

                             

                            No i don't want the line, i want a point. Check the image.Risk-Return plot - dummy values.png

                             

                            Regards,

                            Varun

                            • 12. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
                              Simon Runc

                              Hi Varun,

                               

                              so this one is actually a bit trickier than the Brain Teaser as we're not deriving the "guide mark" from the Risk and Return fields, so we get into the sort of problems this post is about (namely the extra marks generated by double-dual-axis)

                               

                              Attached is a solution where I've used a combination of IF FIRST()=0 THEN...END to get less marks for the "targets", and then also colored on Measure Name, so that I can make any extra marks we don't need white.

                               

                              Hope it all makes sense.

                              • 13. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
                                v k

                                Thanks Simon for your reply.

                                 

                                Looks like you are using a higher version, my work place has 10.2.1.

                                 

                                I will take a look at it once i am home.

                                 

                                Thanks once again.

                                 

                                Regards,

                                Varun

                                • 14. Re: Educational Brain Teaser: Scatter Plot - detail & percentile indicator
                                  v k

                                  Hey,

                                   

                                  Simon.. you are a genius!  Thank you so much!

                                   

                                  I think it has solved one use case for sure.

                                   

                                  However is this solution extendable when we want to color the points (accts) based on condition of assets and return. I have created a scenario for the same in the attached file.

                                   

                                  Thanks for all your help!

                                   

                                  Regards,

                                  Varun

                                  1 2 Previous Next