Different Ranking Scores for Identical Sales Amount with RANKX Function

[monaraya]

In this blog, I would like to address one of the issues that I came across recently with the RANKX function where the Ranking Score is different, even though the Sales Amount is exactly same as shown below:

The measure used for Ranking Score has multiple nested measures which reference the RankX function.

Ranking Score = 

VAR V1=

calculate([Number of Users in selection],all(Table1[Column1]))

VAR V2 = LOOKUPVALUE('Table2'[Column2],'Table2'[Column3],"SellerName",'Table3'[Column1],SELECTEDVALUE(SalesLeague[Region]))

VAR V3 =LOOKUPVALUE('Table3'[Column3], 'Table3'[Column4]," PTQ",’ 'Table3'[Column1],SELECTEDVALUE(Seller[GROUP]))

VAR V4 =LOOKUPVALUE('Table3'[Column6],Table3'[Column7],"Seller",'Weight'[Division],SELECTEDVALUE(Seller[GROUP]))

VAR V5 =LOOKUPVALUE('Table4'[Column1], 'Table4'[Column2],"Seller%",'Table6'[Column1],SELECTEDVALUE(Seller[GROUP]))

return

+((NumberofUsersinselection-[TopSeller rank]+1)* Weight)

+((NumberofUsersinselection -[TopSeller PTQ Rank]+1)* PTQWeight)

+(( NumberofUsersinselection -[TopSeller Growth Rank]+1)* GrowthWeight)

+((NumberofUsersinselection-[TopSeller Growth% Rank]+1)*GrowthPercent)

Upon checking the root level measure, I have noticed that the measure is referencing a table named “Table3” and the data type of the below Columns is a 'Decimal Number': 

• Table3[Column1]
• Table3[Column2]
• Table3[Column3]

RANKX function expects 'Whole Number' as an input and in this case the discrepancy seen is due to the Decimal Number input. So, I went ahead and changed the Data type and Format to 'Whole Number' from 'Decimal Number'. 

And the Ranking Score works as expected now.

Author: Mounika Narayana Reddy