How to leave ranking unchanged after selection using Count Total Distinct minus Rank Avg

[H_M]

Hi all. I have tried to search for similar topics but cannot find set analysis to achieve my specific requirement. Given the following formula how do I ensure when I select one company, the ranking remains unchanged, currently when I select a company it changes the rank to 1:

=(Count({<[Country] = {'Country 2'}>} Total Distinct [Company]))+1-(Rank(Avg({1}{<[Country] = {'Country 2'}>}[Avg % Transaction Cost]/100)))

I have attached a sample of the data and qvf file (although my actual dataset is much larger, with many more countries and many more companies)

[sunny_talwar · Accepted solution]

Is this what you are looking to get?

Basically change your dimension to be just Company and then your expression to be

=(Count({1<[Country] = {'Country 2'}>} Total Distinct [Company]))+1-(Rank(Avg({1<[Country] = {'Country 2'}>}[Avg % Transaction Cost]/100))) * Avg(1)

[H_M]

Thank you so much @sunny_talwar  I see you are a guru in the Qlik community, a lot of your responses on other threads have also helped me. I changed dimension to Company only and used your formula and it's now showing me the correct rank 🙂

[tresesco]

You probably missed the last section of expression and that is the trick which is actually reducing the values in chart.

....* Avg(1)