5 Replies Latest reply on Sep 12, 2018 2:44 AM by Lucie Brett

# Rank and select top N after table calculation

Hello everyone,

I have mocked up what I am trying to achieve in the attached workbook. A client has asked to show the top 3 sub-categories with the greatest absolute (-ive or +ive) YoY sales growth.

I am using a table calc to get the YoY growth (have hidden previous year column), I have then added this calc as a discrete pill to the left of the sub-category wrapped in abs() and with "1-" at the beginning so that it orders from highest absolute % difference to lowest.

Now I need to select the top 3 however I can't seem to get this to work using rank() or index(), due to the hidden column and the table calc I imagine. Does anyone have any ideas how to do this or indeed a better way of calculating?

This needs to be fully dynamic so there will be additional filters which might need to be added to context.

• ###### 1. Re: Rank and select top N after table calculation

Hello Lucie,

Please refer to the attached workbook.

Regards

Lei

• ###### 2. Re: Rank and select top N after table calculation

Thank you, Lei Chen.

Upon trying to recreate what you have done, I have noticed that the sub-category had a manual sort. I need this solution to be fully dynamic unfortunately.

Any other ideas?

1 of 1 people found this helpful
• ###### 3. Re: Rank and select top N after table calculation

Hello Lucie,

You're right.

I previously thought that desc sort would work dynamically, but it turned out not.

When clicked on asc/desc sort of an aggregated measure (without table calculation), the sort is dynamic.

When clicked on asc/desc sort of an aggregated measure (with table calculation), the sort becomes manual

Sorry that I haven't got any idea currently.

Regards

Lei

• ###### 4. Re: Rank and select top N after table calculation

Hi Lucie,

Please try the attached, I've done the same as Lei where I created the absolute difference as a calculated field, then I used rank (or I guess you can use rank unique) with this field.

Thanks,

Mavis

2 of 2 people found this helpful
• ###### 5. Re: Rank and select top N after table calculation

Thanks Mavis, this is perfect!