1 of 1 people found this helpful
What you need is to change the Adressing of your Rank Table Calculation. This way you will give a single rank for every Product without restarting the ranking for every region having sales on the product.
You can also change the order of the region by right-clicking on the Region pill and set the Sort options.
ps: next time try to post a packaged workbook (.twbx) instead of a .twb. even if you use the Superstore.xls. You will increase your chances to get an answer
Thanks for the reply, Michel! I've tried that several times, but it still results in a different rank order than when region is omitted from the color pill. Does this process have to be done in a specific order? I feel like I'm missing a step here.
I've attached the packaged workbook. Thanks for the tip!
Really appreciate the help!
Rank_Test.twbx.zip 1.1 MB
Michel's approach should work. Is it possible that you've got a different setup on the filter calculation?
If you just want to filter to the Top N, there are some other options:
- You can also use a calculated Set to accomplish your goal here. If you right click the Product field you can select "Create Set" and then use the Top tab to create a set based on the Top N products by Sales. You'll have to use an additional calculated field to reference the set and use in the filter.
- You can use a Top N filter on the products field. That's probably the easiest approach.
I've attached a workbook to demonstrate Michel's approach and the other possibilities.
Rank_Test.twbx.zip 419.1 KB
By the way, 8.1 will have a nice RANK function that will make life a little easier!
Excellent! Thanks so much for the reply. The trick was leaving the Sort field "Automatic" on the advanced settings. If I tried to use a specific measure instead of automatic, it would throw the sort off. I really appreciate the help from both of you!
Also, thanks for the heads up regarding the new Rank function coming. I'll be sure to check it out in 8.1.