Create Top N based on additional filtering criteria

[Mal_Sondh]

Hi All,

I have the following example data input (Table Name = :

Name	URLCount
News - Home	100
News Weather	50
Sport - Home Page	1000
Sport Football	950
Sport Cricket	600
Sport Snooker	20

I have applied the following formula to get ranking:

Top Ranked Pages = RANKX(ALL(PageRefTable[Name],[URLCount]),,DESC,DENSE)

And this provides me with the following

Top Ranked Pages

Name	URL Count	Top Ranked Pages
Sport - Home	1000	1
Sport Football	950	2
Sport Cricket	600	3
News - Home	100	4
News Weather	50	5
Sport Snooker	20	6

When i then apply a filter on the data and remove all items which contains "Home", the table then changes as follows:

Name	URL Count	Top Ranked Pages
Sport Football	950	2
Sport Cricket	600	3
News Weather	50	5
Sport Snooker	20	6

As you can see the ranking is then not continuous from 1, because what i want to see in the visual are the top 10 and i would use a condition such as Show me all data where the Top Ranked Pages <=10, in the case above it will not work as the ranking is at a different level.

I need a formula which replicates what i am doing in the visual - by ranking and including a filter criteria to not rank the records that contain the word "Home" - how can this be done?

I know there is a way to add a filter to the query, but what is the exact syntax to get the following result:

Name	URL Count	Top Ranked Pages
Sport Football	950	1
Sport Cricket	600	2
News Weather	50	3
Sport Snooker	20	4

Any help would be appreciated...

thanks in advance.

[Fowmy]

@Mal_Sondh 


Use this measure to get the correct Ranking: 

Top Ranked Pages = RANKX(ALLSELECTED(PageRefTable[Name]),[URLCount],,DESC,DENSE)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[nvprasad]

Hi,

You can get using allselected. Below is the snapshot for your ref.

Function : 

Rank = RANKX(ALLSELECTED(Data),[Urlsum],,DESC)

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

[Mal_Sondh]

@nvprasad @Fowmy  

Thanks for the update - the calculation adjustment works up until this use case where there are multiple records that have the same count - the rank is then always the same.  Is there anyway to always just get a continous RANK rather than duplicates

For instance if the input data was 

Name	URL Count
News - Home	100
News Weather	50
Sport - Home Page	1000
Sport Football	950
Sport Cricket	600
Sport Tennis	600

Then the ranking will come out as follows:

Name	URL Count	Top Ranked Pages
Sport - Home Page	1000	1
Sport Football	950	2
Sport Cricket	600	3
Sport Tennis	600	3
News - Home	100	4
News Weather	50	5

Rank 3 is replicated, is there anyway to Rank based on contunious numbering?

In this example if i say show me <=Rank 3, this will show 4 records as opposed to the top 3 - How can i only show the top 3 in a chart?

Thanks!

[v-jayw-msft]

Hi @Mal_Sondh ,

I doubt you could get that. The question is that "Sport Cricket" and "Sport Tennis" are both third place, which one you want to show on the visual?

Best Regards,

Jay