The issue for the extra space is having Reason on the color shelf. Tableau is finding the 20 rows rsnr Reasons and since they don't match up with the other Reasons it is returning these 'extra' places. The best way around this is to use the top N of the set created or of Product.
Unless I’ve missed something... try this.
- Remove the index from your filter
- Use the set 1
- Add the Reason filter to the context.
For some reason, I can't get my file to load to show the example, but the product sort is from 99 through 203 and matches your top 20 Excel file.
I've uploaded the workbook.
SetWithContextTop 20.twbx.zip 177.9 KB
Placing the set on the filter works because there is a top 20 on it based on sum of Discount Amount. You can see this if you right click on his set and select Edit.
Based on Archana's first question the "Set" wasn't providing the results needed. So looking at the Excel file and considering it the base example that needs to be matched. We find that the first set of data is when the Reason filter is In Context and it matches the Excel example. The second set of data shows the results when the Reason filter is not in Context and about record 12 down, the cases don’t meet the criteria.
The left two columns were in the original post sample file. The last two columns from the workbook with Set 1 and Reason in the filters with the Reason filter added to the context.
Excel File Products Sum of Discount Amount In Context Reason 99 871381 99 871381 105 770814 105 770814 145 719604 145 719604 108 588291 108 588291 156 536276 156 536276 102 530192 102 530192 144 495578 144 495578 78 470596 78 470596 98 468808 98 468808 142 441534 142 441534 141 384726 141 384726 160 352078 160 352078 121 317081 121 317081 163 295797 163 295797 159 295234 159 295234 104 280382 104 280382 227 235456 227 235456 59 226942 59 226942 216 210045 216 210045 203 199462 203 199462 Match Check
(x doesn’t match original)
Not In Contex Reason Ok 99 871381 Ok 105 770814 Ok 145 719604 Ok 108 588291 Ok 156 536276 Ok 102 530192 Ok 144 495578 Ok 78 470596 Ok 98 468808 Ok 142 441534 Ok 141 384726 X 121 317081 X 163 295797 X 104 280382 X 227 235456 X 216 210045 X 51 192733 X 162 118911 X 155 99149 X 198 72095
Thanks a lot guys! That Definately helped. Just wanted to know what exectly a context filter does? Does it affect other sheets in the same Dashboard?
Ah, I see, I missed that the results were different! Guess I didn't read the original post close enough! The following link provides more information about context filters:
The context filter will only effect the other sheets in the dashboard if it is a global filter as well.
When thinking about whether or not to use context or not is what business question you are trying to answer. I know we are playing with dummy data, but you have product with discounts and reasons.
By using context the question might be. Only considering these reasons, show me the sum of discounts within these reasons and order them descending by discount amount.
Product 144 with reasons (rsnc, rsnd, rsnf, rsnl, rsno, rsnr) $495,578
Not using context you note you were getting a different result. The question would be: show me the top 20 products that have the highest discounts (regardless of reasons) and then filter / focus on these reasons (rsnc, rsnd, rsnf, rsnl, rsno, rsnr).
Product 144 regardless of reasons $2,889,465
That is why at the end you are getting different results. Beginning with record 12 (above post) you are getting results that in total had higher discounts than the ones being returned by Excel’s pivot table or the “context” filter.
Just some thoughts so that your end product meets your needs and when you would use Context vs No Context.
Attached is another option. This used a custom Index formula, and then a little trick to get Tableau to perform partitioning that is not available in the user interface.
Top 20 jm edit.twbx.zip 187.4 KB