
1. Re: Educational Brain Teaser: Scatter Plot  detail & percentile indicator
Simon Runc Jun 5, 2015 8:00 AM (in response to 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 'approachIthoughtwouldwork' 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 Jun 5, 2015 2:07 PM (in response to Simon Runc)1 of 1 people found this helpfulHi Simon,
Thank you for the teaser.
Give it 2.5 (got success from my second guess :)
Yours,
Yuri

3. Re: Educational Brain Teaser: Scatter Plot  detail & percentile indicator
Mark Jackson Jun 5, 2015 2:10 PM (in response to Yuriy Fal)1 of 1 people found this helpfulI 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.

4. Re: Educational Brain Teaser: Scatter Plot  detail & percentile indicator
Jonathan Drummey Jun 6, 2015 6:59 AM (in response to Simon Runc)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.
The basic "trick" used to build this is extensible to a number of other problems in Tableau...I see some blog posts coming...
Jonathan

N+M Marks with v9.twbx 37.3 KB


5. Re: Educational Brain Teaser: Scatter Plot  detail & percentile indicator
Simon Runc Jun 8, 2015 1:57 AM (in response to Jonathan Drummey)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 Jun 10, 2015 3:06 AM (in response to 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.
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, 85^{th} Percentile), it actually plots a mark for 85^{th} Percentile Mean (at every SUM([Total position]), and viceversa for Total.
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.
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.
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 visualtrickery 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 85^{th} Percentile twice, but within a dualaxis 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 Jun 10, 2015 9:23 AM (in response to Simon Runc)1 of 1 people found this helpfulThank 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!

8. Re: Educational Brain Teaser: Scatter Plot  detail & percentile indicator
Simon Runc Jun 10, 2015 9:48 AM (in response to Bill Lyons)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'!!