See attached. Recreated Jonathan sheet but instead of using your 'rank' calculated field, did the following:
1. dragged Sum(Sales) into columns
2. converted to discrete
3. edited Table Calculation, by doing right-click on Sum(Sales)
4. choose 'Specific Dimensions' option
5. Unticked Manufacturer, leaving Sub-category only as ticked
Hope this answers your question.
I followed your procedure but it didn't work
Thanks for the reply!
Can we do it using Rank function instead of Index.
I tried it using Rank but couldn't do it
The calculation I set up in the attached workbook, will give you the correct result. You can try using rank and let me know.
Can you send over your file again ?