1 2 Previous Next 16 Replies Latest reply on Mar 9, 2018 5:33 AM by Matt Spicer

# Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

I have a data set that is a large commodity spend data set with different sites (buying locations/Display Name),Suppliers, Commodity Types over a several year period.  I am trying to create a view that has just the top and bottom 10% of Suppliers based on Spend, Display Name, Commodity Class and a filtered time period.  In addition I have ranked the Suppliers in the top and bottom 10% from largest spend to smallest spend.  I am having a couple of issues.  One is how to show a supplier only once when it may be in the top 10% twice but with different Display Names (Purchasing Sites).  The second problem is how to get the real top and bottom 10% when my data is filtered by all the different criteria that I am using.

For the rank, I used the following formula: rank(sum({Fixed[Supplier Name],[Display Name],[Date Received],[Commodity Class Description]:SUM([Unit Qty Price Total])}))  Unit Qty Price Total is actually the Spend

For the top and bottom 10% I tried: IF RANK(SUM([Unit Qty Price Total])) <= 0.1*TOTAL(COUNTD([Supplier Name])) THEN 'Top 10%'
ELSEIF RANK(SUM([Unit Qty Price Total])) > TOTAL(COUNTD([Supplier Name]))-(0.1*TOTAL(COUNTD([Supplier Name])))
THEN 'Bottom 10%'
ELSE 'Hide'
END

The formula top and bottom 10% is not working properly and I knew it wouldn't but nothing I tried seemed to work.  Does anyone know how I can correct my formula or a more efficient way to accomplish this?

• ###### 1. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

Hi Matt

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

Jim

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.

1 of 1 people found this helpful
• ###### 2. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

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.

• ###### 3. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

I'm sure you'll figure it out

• ###### 4. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

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.

• ###### 5. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

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

• ###### 6. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

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.

• ###### 7. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

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

JIm

• ###### 8. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

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.

Regards

• ###### 9. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

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

• ###### 10. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

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.

Regards

• ###### 11. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

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

• ###### 12. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

Jim, thank you so much for what you have done it is greatly appreciated.

Regards

• ###### 13. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

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.

• ###### 14. Re: Trying to create a top and bottom 10% of Suppliers Based on Spend, Date and Commodity

Hey Jim, do you have an answer on the below issue?

Regards

1 2 Previous Next