RankX Measure re-calculating in table when filtering?

[Anonymous]

Hi all,

I have an issue I can't quite figure out. 

I have two matrix in my report, one contains the measure "Prod ADV % Rank = RANKX(ALL('Pols Outcome and Inception'[Product Code + Scheme fin]),CALCULATE([ADV%]))". For some reason I can't sotp it re-calculating the rank whenever I select the "Product Code + Scheme" table below that shows month (shown in picture 2)   

Whereas in different version of this measure I don't have the same issue? See below!

I imagine this is a quick fix, any help appreciated!

Kind regards,

Jordan

[Anonymous · Accepted solution]

Having spent a long time invesigating, it appears that the measure works with just "Product code" and the addition of "product code + product scheme" is what breaks it! Suggestign the measure was fine and the underlying data contains the issue. To combat this, I've created a hierachy with product code -> product scheme, which allows us to still see the scheme attached to the code after a simple drill through click!

[v-gizhi-msft]

Hi,

Please try this:

Prod ADV % Rank =
RANKX ( ALL ( 'Pols Outcome and Inception' ), CALCULATE ( [ADV%] ) )

If this can not solve your issue, please share some sample data and 'ADV%' measure formula as a screenshot for me to help you further.

Expect your reply!

Thanks.

Best Regards,

Giotto Zhi

[Anonymous]

Hi Giotto,

My existing measure already is  

Prod ADV % Rank =
RANKX ( ALL ( 'Pols Outcome and Inception' ), CALCULATE ( [ADV%] ) ) 

I'll PM you a link to the file so you can see for yourself, this is probably easiest! 

Kind regards and thank you for your help,

Jordan  

[v-gizhi-msft]

Hi,

Please make sure that you have removing any sensitive data in your shared file.

And i advise you sharing your file here instead of personal private messages.

This can let me help you further.

Thanks!

Expect your reply.

Best Regards,

Giotto Zhi

[Anonymous]

Hi Giotto,

Link is attached with all sensitive info removed!

 You'll be able to see the two tabs that are meant to built the same, just with different columns. The first tab "Policy FLT" contains the RankX measure that works corrcetly in the ADV% Rank matrix. When you click the larger table below "Main account name + FCA" and select a single row, this filters out into the ADV % rank to show the same single row, but it doesn't filter the rank e.g. clicking on "A One Insurance servcies -- 307536" in the "Main Account Name + FCA" matrix, will display "A One services -- 307536 - Pols ADV% Rank 137", whereas if I did these exact steps in the matrix within "Product FLT" it will recalculate the ADV% rank and make the single entry Rank 1.

Any help here would be greatly appreciated! 

https://www.dropbox.com/s/52xs398a2v1wa0h/File%20Mar%2011%2C%2011%2020%2014%20AM?dl=0

Kind regards,

Jordan

[amitchandak]

@Anonymous 

When you choose categorical Rank, then the rank will change if you use any other view of data.

Prod ADV % Rank = RANKX(ALL('Pols Outcome and Inception'[Product Code + Scheme fin]),CALCULATE([ADV%]),,desc,dense)

For Rank, also Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

Appreciate your Kudos.

[Anonymous]

Hi Jordan,

I believe the measure is being filtered when you filter the results. The way to avoid this would be to specifiy in the calculate part of your measure that you want it to be for all data in your table

Prod ADV % Rank = RANKX(ALL('Pols Outcome and Inception'[Product Code + Scheme fin]),CALCULATE([ADV%]),All('Pols Outcome and Inception'))