Need RANKX to ignore Legend/Columns on visualizations

[NewAnalyst16]

Hi,

I've been using a RANKX Measure to rank plans my company offers by the total sum of contracts that exist on each plan. I use this so in the report we can narrow down by top 5 largest plans, top 10, etc.

Plan Rank = rankx(ALL('Residential - US South'[Plan Name]), SUMX(RELATEDTABLE('Residential - US South'),'Residential - US South'[Contract Size]))

However, I've ran into an issue when using this Measure on visualizations where apply a Residential - US South'[Renewal Status] either column or legend which shows what percent of the contracts have been renewed, and what percent have not. When I do this, the rankx measure ranks both the Renewed Contracts and the Non Renewed Contracts, so the plan now has two seperate rankings which messes up my filtering down capabilities.

I need help writing the expression so it ignores the columns or legend applied (while still reacting to filters on the page), so each page has one set ranking based on their overall sum of contracts, regardless of whether they have been renewed or not.

Thanks!

[v-lionel-msft]

Hi @NewAnalyst16 ,

Has your problem been solved? If not, please provide sample data or sample .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @NewAnalyst16 ,

The use of measures is based on context meaning that the calculation is done based on the filters, columns, visualizaitons, interactions and so on, however you can overcome that by adding the ALL syntax for example has you did.

However in this case since you are adding a new column to your visualization you need to add it to the RANKING measure.

Please check this blog post with an explanation about the use of RANKX with several levels of detail:

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

If you still need assistance with the calculation please tell me, and if please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.