Hi Sudheesh! I think that there are a number of ways of doing this, but the easiest way is probably to use sets. Just set up a set that finds the top 5 Order IDs by discount.
Then, right-click and hold on the set you've just created and drag it into filters. Pick In/Out from the options, and filter it to only include "Out". That should do it.
@David's approach will work when there is a context filter on the Product Category that only includes a single category, otherwise the Top N could end up including product IDs for other products. Tableau's Top N filters on dimensions do not currently act as a "Top N for each".
@Sushma's approach is heading in the right direction but needs a little more work, I used his workbook to set up the following. If you want to show the average for each category with multiple categories in the view then you'll need to use a table calculation. In the attached I set up an RU Discount field with the formula RANK_UNIQUE(SUM([Discount])) with a compute using on Order ID so that it ranks each order ID based on the Discount (to show you what the rank unique is doing), then WINDOW_AVG(IF RANK_UNIQUE(SUM([Discount])) > 5 THEN SUM([Discount]) END) that also has a Compute Using on Order ID gets the WINDOW_AVG across the remaining rows.
Finally because it's somewhat likely that you'll just want one result per Category I created one more calc that has the formula IF FIRST()==0 THEN WINDOW_AVG(IF RANK_UNIQUE(SUM([Discount])) > 5 THEN SUM([Discount]) END) END that also has a Compute Using on Order ID, this returns a single result.
Then in the final view put Order ID on the Level of Detail and a copy of the table calc on the Filters Shelf filtering for non-Null values to get rid of the Order IDs that we need for the table calc to work:
This is essentially a variation on a TRIMMEAN() function, I recently wrote a post on it at TRIMMEAN() in Tableau | Drawing with Numbers.
v9.3 workbook is attached.
top n for each 20160822.twbx 75.0 KB
What should be my approach, if instead of constant of top 5, i want to do it by percentile.. say.. exclude the top 5th percentile and perform average calculation on rest of the population?
I tried applying the logic from your trim mean blog but it is not giving me the results I am expecting.
Given the multiple steps involved I can't diagnose your problem without a lot more detail. The best would be for you to post a Tableau packaged workbook with some sample data and your efforts so far along with the results you are expecting.
Sent from my iPhone
Sorry for the delayed response. My HDD crashed and lost all my work. Took some time to set back up everything from backup.
I actually wanted to try and do it myself and then come here if I couldn't. I managed to do it by understanding the logic and sense in your TRIMMEAN() in Tableau | Drawing with Numbers which I was not applying correctly before. Brilliant technique and detailed explanation helped me achieve this.
I was trying to do a cluster analysis on discounting and I wanted to remove the discount outlers and then get the average of the data set. For Visualization I created box and whisker to show the spread of the data points. In addition, I used parameter control to give option to the user to choose the Upper and Lower Percentiles so they can select what percentile needs to be excluded.Thanks a ton again Jonathan Drummey