10 Replies Latest reply on Jun 15, 2018 1:22 PM by Reporting DAD

Getting Top and Bottom 3 using 2 measures as a base

I have data as shown below

 Name Revenue Spend CR A 5 10 2 B 10 5 3 C 25 30 8 D 15 50 4 E 35 36 1 F 45 40 7 G 60 55 6 H 20 80 10 I 70 75 15 J 65 90 20 K 55 45 14 L 80 70 11 M 40 30 9

Now I wanted the names with Top 3 revenue. I sorted and used Index to achieve the same.

Now I have 2 more things to do.

First I wanted top 3 names with highest CR, but it should be exclusive of the top 3 names with highest revenues found above with index.

Second I want the top 3 names who has spend the highest but has the lowest CR.

Can this be achieved?

Added : The data also has some more columns why which it can be filtered. Also everyday the data will keep getting added to this. The above is just a sample. Please is the attached workbook.

• 1. Re: Getting Top and Bottom 3 using 2 measures as a base

Make a RANK calc for all three columns.  (Separate calcs).

Once you have that, you can make one more calc that grabs the top-3 of this-rank and the bottom-3 of that-rank, and you can get the top 3 that are not in the top-3 of some other rank...

If you need help, post a sample workbook.

• 2. Re: Getting Top and Bottom 3 using 2 measures as a base

Hi, Please see attachment. You can resolve the problem only by set.

Key:

(1)  Building several set.

(2) Most importantly, building the set: "Non Top 3 Name by Revenue"  and put in filter, set it using context filter(Otherwise, you cannot get top 3, you will only get 1, which is K)

(3) Building a calcs: difference between Spend and  CR:

Sum([Spend])-SUM([CR])

to find out top 3 who has spent the highest but lowest CR.

Hope it helps.

Michael Ye

4 of 4 people found this helpful

• 4. Re: Getting Top and Bottom 3 using 2 measures as a base

Your solution seemed helpful. Only problem I am facing is that when I add any filter, the set will not show the top 3 of that filter. Also, in one of the sets I have to mention the bottom 10 rows. But my data will keep growing everyday. I cannot hard code the number of rows then.

• 5. Re: Getting Top and Bottom 3 using 2 measures as a base

I opened your workbook.  I get this message:

When I clear the error I get a blank sheet.  Then I tried taking table calcs off the sheet and other things to get something to work.  My last resort was to do a "View Data" so that I could cut-and-paste it into excel and make a new data source.

I get this message:

Can you either hack up a simple data source in excel, and then do REPLACE DATA SOURCE with that new data source, or try extracting the data source and using that?

• 6. Re: Getting Top and Bottom 3 using 2 measures as a base

I have changed the data. Can you access the data now?

• 7. Re: Getting Top and Bottom 3 using 2 measures as a base

I'm in!

• 8. Re: Getting Top and Bottom 3 using 2 measures as a base

In the attached I created my own RANK calcs.  Each is displayed after the original measure.  RANK is an interesting creature. There are different types of RANK (such as RANK_DENSE, RANK_UNIQUE, RANK_MODIFIED ...  Go into the calc and change "RANK" to "RANK_" (with the underscore) and tableau will give you the list of other RANK types, and you can select them individually and the little help box to the right of the editor will explain the difference.  Mostly it's about breaking ties -- such as you have.  For now I just used RANK, but you may want to use something else depending on your business needs here.

OK, so a straight RANK puts numbers on all rows.  But look what I did with [Rank not top Revenue].  Here is where you'll exclude your top-3 Revenue values, and you can see the results on the sheet.

Next I added [Get bunches of 3].  For now I just set my target values to 1.  If I put that on FILTERS and selected for 1, I would get those rows on the sheet.

I'm not sure what you mean by this:  "Second I want the top 3 names who has spend the highest but has the lowest CR."  You have 2 rows with the absolute lowest CR:  "E" and "EE".  You'll want to define what "lowest CR" means.  Maybe the bottom 5 CRs?  Or do you want to rank the [Spend] values first, then take maybe the top-10 of those and grab the lowest 3 CRs among those top-10?  These are all rhetorical questions for you to decide.  But in the end you'll make a [RANK SPEND for bottom CR] calc something like my [Rank CR not top Revenue].  and once you have that, you'll add the top-3 of those to the [Get Bunches] calc.  Once you have all your 1s collected from there, you put that on FILTERS and that will display only those rows you want to see.

5 of 5 people found this helpful
• 9. Re: Getting Top and Bottom 3 using 2 measures as a base

PS:  Notice that [Index] isn't even used in here at all.  That was dependent on the sorting of [Name], but really you need independent RANK calcs for each measure you want impacting in the final display.

1 of 1 people found this helpful
• 10. Re: Getting Top and Bottom 3 using 2 measures as a base

That worked perfectly for me. Also, thanks for providing a detailed explanation.