RANKX breaks when adding a field to a visual

[dparkinson]

I've been avoiding the use of RANKX because on previously looking at it, it's behaviour seemed so complicated as to make it unusable.

I have a basic table that has a column for department revenue and I'd simply like to show the top 10 items in the table based on that revenue.  The measure I have is:

Rank of Opportunity by Dept1 Revenue = RANKX(ALL(Opportunities[OpportunityId]),[Total Dept1 Revenue])

This displays fine on a visual if all I have on that visual is the Measure, the Opportunity ID, and the Dept1 Revenue.

As soon as I add the Company Name to the visual, it breaks the Ranking.  

I've told rankx to rank based on opportunity ID which is unique for each opportunity in order to avoid any grouping.  Adding the Company Name doesn't change the value of Total Dept1 Revenue.

Clearly this is probably because the rank isn't specifically based on the Dept1 Revenue column but across all columns?  How can I force RANKX to rank a row based only on a single column?

Any assistance greatly appreciated.

Thanks.

[v-ljerr-msft · Accepted solution]

Hi @dparkinson,

---

---

How can I force RANKX to rank a row based only on a single column?

If I understand you correctly, the formula below should work in your scenario. 

Rank of Opportunity by Dept1 Revenue =
RANKX (
    ALL ( Opportunities[OpportunityId] ),
    CALCULATE (
        [Total Dept1 Revenue],
        ALLEXCEPT ( Opportunities, Opportunities[OpportunityId] )
    )
)

Regards

[Greg_Deckler]

Been trying to replicate this but can't seem to. Can you provide sample/mock data?