
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'!!

9. Re: Educational Brain Teaser: Scatter Plot  detail & percentile indicator
v k May 24, 2018 7:01 AM (in response to Simon Runc)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 coordinate  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

10. Re: Educational Brain Teaser: Scatter Plot  detail & percentile indicator
Simon Runc May 24, 2018 7:12 AM (in response to v k)
Scatter Plot with Zones.twbx 18.7 KB


12. Re: Educational Brain Teaser: Scatter Plot  detail & percentile indicator
Simon Runc May 24, 2018 10:35 AM (in response to v k)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 doubledualaxis)
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.

Scatter Plot with Zones.twbx 24.3 KB


13. Re: Educational Brain Teaser: Scatter Plot  detail & percentile indicator
v k May 24, 2018 9:45 AM (in response to Simon Runc)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 May 25, 2018 12:38 AM (in response to 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