See the attached potential solution
I took a different approach - I made your ranking calculation 2 separate fields - one asc and the other desc
then added a filter
The viz looks like this
each of the table calculations are calculated DOWN
You can hide the 2 ranking formulas
by unclicking show header
and this is the result
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Top-Bottom 10% Help_v10.2.twbx 17.7 MB
Jim, thanks for taking the time to look at this. What I really want to get to is the rank of the top and bottom 10% based on the filtered criteria not just the top and bottom 10 suppliers. The viz is below. My problem is that my formula to get to the top and bottom 10% is not taking into account the filters I have applied for Display Name, Commodity Class Description and Date. Therefore my top 10% is actually more than the total of the filtered Commodity Class "Mechanical Assembly", "Display Name" "West Point" and the time period selected.
I am unsure how to make that formula work for what I am trying to accomplish. I was thinking some kind of IF RANK formula with FIXED dimensions but I cannot get it to work.
I'm sure you'll figure it out
Jim, since I am new to using this forum and I see you are an expert user can you tell me what is the best way to post to get more people to respond? Is my issue not explained clearly or am I asking for too much? I am not sure at this point where to turn.
Good question Matt
No problem with the question you posed or the inclusion of the workbook
I would suggest that you be open to look at the problem in a way that would be different than what you thought
The issue in your workbook has to do with the order of operation internal to Tableau -
When you used Rank it creates a table calculation and those are performed almost at the end of all operations
Other calculations, including OLD's, and all filtering precede the table calculations - ok so what
It means the approach has be different than what you invisioned
The response I sent you used 2 calculations based on rank - and note the calculations were not at the supplier level but at the filtered level of the data
The Issue you added with need sums of the top and bottom 10% represent a significant change - totally table calculations are not done with Grand total and in this case will require some work
I'll take a look into it and try to come up with something that will work
Jim, thanks for answering. I thought it might take a brand new approach. I first tried to go down this approach but it seems that I am just missing something little each time.
The other way I tried to use is this thread:
Both seem to use different approaches and I have spent several hours on something that in my head seems simple but I am always getting stuck with order of operations. It is difficult migrating from excel. A whole different way of thinking.
Thanks for helping. If you can provide something it will be greatly helpful.
I like the first approach but I am having difficulty with the total number
help me understand your need - are you interested in
1 - the top 10 and bottom 10 suppliers overall
2- the top 10 and bottom 10 suppliers in each year
3 the top 10 and bottom 10 combination of suppliers/display/commodity for each year
the first approach is looking at #3 above but if you interest is at the supplier level overall or by year that is different
So the interest is the top and bottom 10% of Suppliers by the amount of spend depending on what the selection/filter is for Display Name, Commodity and Date. In other words, I could filter by any of the commodities, display name or Year (Our fiscal year starts in October but I should have that set already) and I want to see the top and bottom 10% suppliers ranked by spend (which is the sum of Unit Qty Price Total.) Does this make sense? The Display Name, Commodity and Date Received are almost like dynamic filters for the top and bottom 10% of Suppliers by spend.
I hope that is clear.
Here is the difference
this view is the top and bottom 10 suppliers overall - I can lock in the supplier list and use it on all your other worksheets so you would be answering the question of out top and bottom 10 suppliers - who provided displays, commodity etc and how much did they spend
this view looks at the detail level of the those suppliers who provided displays by commodity etc ---what were the top and bottom 10 displays and who provided them
note 1 and 6 are Endine
Either way there is a solution
Jim, the second view is what I would be looking for but instead of the top and bottom 10, I need the top and bottom 10% of Suppliers so the number of suppliers would change depending on the other dimensions.
the attached will provide top 10% and bottom 10% groupings that are dynamic wrt to filtering
or at a summary level
Still not getting totals for the 2 groups separate
Jim, thank you so much for what you have done it is greatly appreciated.
Jim, I have one more question. The below highlighted amount is based on the same criteria on the detail sheet you built. I know our commodity managers like to see how many suppliers make up the bottom 10% of spend. Is there a way to build off what you have already done to see how many and what suppliers make up the bottom 10% of spend. In other words, the jd group bottom 10% of suppliers equals 10% of $24,875,757 or $2,487,576? They use this data to rationalize the supply base as there is usually an extremely large number of suppliers that make up the bottom 10% of spend.
image002.jpg 33.1 KB