RANKX | Question: How do I fix the ranking when there is blank data?

[Amerikaner]

Thank youfor your help in advance. It may be something very simple I overlooked, but it is yet to be fixed.

Referencing the Attachment Below:

• A + B – Two sets of DAX calculations for the TSR% and RANKX function.
• Illustration 01 – Results for “A or B” Dax calculations for 2023
• Illustration 02 – Results for “A” Dax calculation for 2022
• Illustration 03 – Results for “B” Dax calculation for 2022

My focus is on Company “G”. Data was available for 2023, but not available for 2022.

• Illustration 02
  • TSR% = 0% because it is either calculated to 0% or blank
  • RANKX places it at “7” because 0% is higher than the negative returns of some of the other companies
  • Rank 7 =  100% Target
• Illustration 03
  • TSR% = 0% because it is either calculated to 0% or blank
  • RANKX places it at a blank position at the top
  • Non-Rank = 0% Target
• My Issue
  • The calculation still identifies Company G in position 7 (apparently).
  • Impact 1 – 7 is gone from the Rank meaning a 100% Target is Missing
  • Impact 2 – Company Rank 8 (60%) should be Rank 7 (100%)
  • Impact 3 – Company Rank 9 (20%) should be Rank 8 (60%)
  • Impact 4 – Company Rank 10 (0%) should be Rank 9 (20%)

[Anonymous]

Hi, @Amerikaner 

Already aware of the current problem you are facing, could you please share the pbix file without sensitive data for testing purposes, it will help you solve the problem faster. Feel free to help you.

Best Regards,
Yang
Community Support Team

[Amerikaner]

Hi Yang,

Sorry, I can't send the .PBIX as there is too much confidential financial data and it would take too long to scrub. 

TMS