5 Replies Latest reply on Oct 30, 2015 6:08 AM by Toni Jormalainen

# How to calculate different level ranks?

I'm trying to use ranks in my charts but I'm having some problems... I found this thread very useful Ranking -  Rody's workbook has helped me a lot.

As attached workbook shows I have managed to calculate ranks in Solution1 worksheet. In Solution2 I'd like to see first ranks as per category (as the same as in solution1), second rank as per sub-category. I have no idea why I added index there, maybe I thought it might help First ranks should be:

Technology 1

Office Supplies 2

Furniture 3

• ###### 1. Re: How to calculate different level ranks?

Hi Toni,

This is all to do with how the table calculations are being scoped.  I changed the scope of the Sales Rank pill to address [Category].  Right-click the pill, select 'Edit Table Calculation'.  You can see how it's put together there.

Cheers,

Tom

InterWorks

• ###### 2. Re: How to calculate different level ranks?

Not sure if your solution is working correctly. For me atleast in your solution Technology gives rank 1 but others seems not working. Furniture gets rank 2 and 3. Office Supplies gets ranks 1, 2 and 3.

• ###### 3. Re: How to calculate different level ranks?

That's because, the calculation works based on the visible results of your query.  Furniture is the second [Category] seen, so it is indexed at 2 instead of ranked at 3 due to Furniture overall sales.  If you need to rank the category sales, it would be easy enough using a RANK function in SQL.  I'll toy with it in Tableau, but not sure about this layer.

• ###### 4. Re: How to calculate different level ranks?

I think you can calculate total sum per Category and after that use rank. I managed to do this in data model already and it's working in report as well. I'll try and test my solution in Tableau later.

• ###### 5. Re: How to calculate different level ranks?

This was very interesting problem. I suggest that you start solving your problem one step at the time. I wanted to see how the sums of Sales are calculated on my report and after that I just added ranks. Here are some calculations that I made. I also added my workbook if you like to see my calculations.