I will try and have a look for you, but I'm up to my eyeballs at the moment with some App Store submissions (I'm also an occasional iOS/Android developer) so it won't likely be until Monday...Apologies
Mark Fraser pinging myself as a reminder
Thank you both very much.
Apologies, bit delayed looking at this... My suspicion is that its the way Tableau is calculating the median (vs your expectations)
(you have a lot going on in your question, so any samples you could provide would help, a lot!!)
But for now... I have been playing around with medians and you do have to be careful with Medians
From here >> How to Find the Median Value
Now in Tableau
Apparently (and I need to do some more work on this) Tableau sees this list 3 5 7 12 13 14 21 23 29 40 56
And this is why the median comes out as 14
But bringing in the number of records, we know that actually are list has more values (the repeating 23) which aren't being taken care of...
With an odd length list its fine...
Personally i would have a look at creating the 'median' myself first, so I can be sure of the calculations rather than using the inbuilt calcs
There maybe something useful in this thread Means, Medians, and Modes...oh my
If you could provide any sample it would certainly help!
Thanks very much Mark. I've created a sample workbook for your review. My logic falls apart (does not return employee(s)) when there are 2 median employees with different salaries/total cash comp. You will see this when you manipulate the filters.
1 of 1 people found this helpful
Apologies for the delayed reply, I'm on holiday next week and a little busier than normal
I have had a (admittedly brief) look at the workbook and I think the issue is actually described here...
However, this only works if there is a single median employee or multiple median employees with exactly the same cash comp. If the calc returns more than 1 median employee who have different cash comp calcs, the cross tab returns no one.
What (I think) happens -
It is working when you select individuals (or like for like values) because its effectively saying - do I equal myself? and the answer is yes, so it returns.
But when you add more employees - you amend the value of WINDOW_MEDIAN value to a value which isn't now present, and try and test against it (as an exact match) < that won't work, as you have found.
I will try and explain with examples:
Emp 1 USD = 1 Median is therefore 1 1=1 is OK
Emp 3 USD = 5
Emp 4 USD = 5 Median is 5 5=5 is OK
But you add in a 2nd non match, this means a variation of the median is calculated, and therefore no longer matches (i.e. no longer satisfies the Median/ Not median test)
Emp 5 USD = 8
Emp 6 USD = 10 Median (should be) 9 8 /= 9, 10/= 9, nothing is returned.
I think what you'll need to do, is to revise the below to include some kind of range
IF ROUND([Total Cash Comp (USD)],0) = ROUND([Window Median],0) THEN 'Median' ELSE 'Not Median' END
So in my example, if we said USD >= MEDIAN -1 and USD <= MEDIAN +1 then it would have satisfied both results and returned.
I hope the above makes some kind of sense, and I hope I have correctly identified the issue, currently it seems to make sense to me, but I'm conscious I could be completely wrong!!
As mentioned, I'm away next week, if you need a hand, I would suggest Simon Runc, that guy is SMART, and extremely nice & helpful!
Hope that gives you a start, I'm off for a lie down
Thanks again Mark. You're spot on with the details of my problem and I think your solution is a good start. I'm testing the following formula:
IF SUM([Annual Base Amount (USD)]) >= [Window Median - Ann Base] -1
AND SUM([Annual Base Amount (USD)]) <= [Window Median - Ann Base] +1
THEN 'Median' ELSE 'Not Median' END
However, the way I've applied (probably incorrectly) it adds or subtracts 1 from the result of the window median and returns more than 1 result. My thought was that we want the "+1+ or "-1" to return the values that are up 1 and down 1 within the median. Basically, I don't want the median to behave the way it's supposed to (haha) but instead return the 2 different median values that Tableau averages.
1 of 1 people found this helpful
Thanks Mark...and no-pressure on being SMART enough to find a solution!!
So I've only had a quick look at this, and only have a few mins now...I'll can go into more detail when I get sometime tomorrow (let me know, if you need this).
So we won't be able to change the way tableau (or any other software) would return the median value (...in that it is correct!). As I've noticed that you have [Emp ID] in your table, it gave me an idea!...we could get to the Emp ID you need by using RANK and COUNTD to determine the middle 1 (if odd COUNTD) and the middle 2 if it's even.
The following calcs are a little complicated, but hopefully the Table I've built on 'SR' tab, helps explain...
So first I rank the [Emp ID] on 'Base Ann'
[Rank - Ann Base - SR]
RANK_UNIQUE(SUM([Annual Base Amount (USD)]),'desc')
Then I get a COUNTD of all the [Emp ID] that are in the view (i.e. after filtering)
[Number of Emp ID Selected - SR]
and then we use these 2 values to determine in the [Number of Emp ID Selected - SR] is odd or even (that's the %2 part...this is the Modular function)...and then we can match the RANK to return the correct [Emp ID](s). In my example I've marked the ones to return with a 1
[Return Emp ID - SR]
IF [Number of Emp ID Selected - SR]%2 > 0 THEN
IF [Rank - Ann Base - SR] = ([Number of Emp ID Selected - SR]/2)+0.5 THEN 1 END
[Rank - Ann Base - SR] = ([Number of Emp ID Selected - SR]/2)+1 OR
[Rank - Ann Base - SR] = ([Number of Emp ID Selected - SR]/2) THEN 1
Hope that, sort of, makes sense. Let me know if you think that'll work for you, and if not why...and also let me know if this works, but doesn't make any sense (and I'll give a more detailed explanation)
...nice little challenge this!
2 of 2 people found this helpful
You may want to use a "regular" aggregate MEDIAN() calculation.
For that you would not be using Table Calculations, but -- given [Emp ID] --
opt to using Row-Level and FIXED LODs (before aggregates) instead.
It means that you'd be using your Filters mainly as Context ones.
Please find the attached w/Sheet 7 as an example.
Hope it could help.
Simon and Yuri,
Thanks very much for your work on this. I can't wait to try your solutions! Any chance either of you will be attending the Tableau Conference this year? If so, I owe you both a pint.
Andy, you're welcome.
Pity I would not attend the TC16,
so you owe two pints to Simon :-)
I'm still 50/50 in TC16 (...in delicate negotiations with the boss)
...but if I'm successful, I'll (Oscar-style) accept the 2nd beer on Yuri's behalf
Hi Yuri, I'm working through your solution and so far, so good! I want to thank you again for your guidance.