Well that was a bit of fun, Andy - you're good at posting learning opportunities. Take a look at [sheet 2]. I summarised the others using WINDOW_AVG(AVG([Sales]) - not sure if that's what you want, but you get the idea.
Edit: Just figured out what the z was for in "z other" - I took that off and broke the sorting when you change the parameter. Have now added a hidden sort field.
Top n RL.twbx 984.7 K
What if you added an additional flag that followed the logic of
If Value is in the top N then return Customer_Name
else return "OTHER"
and use that as the column pill
I would imagine something like that would work.
edit: Nvm, that is exactly what Richard did.
Richard - that's incredible. I still don't have my head round WINDOW_ functions. It's great to see WINDOW_AVG being used to pull info from multiple records into one cell.
Thanks very much.
The next question was going to be - can I now create a hierarchy so that I can expand/contract "Others". I think that's one step too far as the formulae create Measures, not Dimensions. Instead, I'll build an Action that will open another sheet to show Others' details.
> can I now create a hierarchy so that I can expand/contract "Others"
If it's miracles you want you're going to need to consult Joe. ;-)
Here it is as a parameter (I modified some of the calcs).
As Christian Chabot said at the customer conference, "Your first question need no longer be, is it possible?"
"Assume it is and begin."
This would not be possible without Table calcs and parameters, I say it is the developers of Tableau who are the miracle workers.
Joe - you're a legend. Tableau developers - you're also legends. (you, too, Richard!) Hey, everyone's a legend!
This is amazing. I will be using this technique a lot. Thanks guys!
I've done a blog post on this solution:
I haven't explained it, though. It's too complicated for me. However, I made it a bit prettier!
Sorry about this beginner question,
On Top n RL with condense option.twbx's sheet 2, Sort order and Custemer name are hidden. Both elements are in bold. What is the way to hide elements like this?
Right click on the field pill on the shelf and unselect "Show header" (which actually means "show column".
They are bold because they are sorted, but hidden because they are only needed for the sort order.
This is extremely useful. I actually thought that there was no feasible way to accomplish this. Well done gentlemen.
Have you given any thought to how this could be accomplished with many measures--say sales and profit perhaps? How about with a date dimension member such as QTR on the columns shelf?
Good job guys - this should be a feature for "Top N" filtering. A simple checkbox (include "others"?).
If you provide some sample data and details on what you want the result of different selections to be, I expect an example solution could be made.
I am using the original example to select top vendors by spend and it is great
Can anyone tell me what I am doing wrong here I am using the top ( ) example and trying to get a total of the top ( ) in the chart using this table calc to use as a total of the top( ) but it gives the total of the top ( ) and others
If [Sort Order]=1 and [Rank]<= [Top ( ) Master Vendors]