Different Ranking Scores for Identical Sales Amoun…

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