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
Be the first to comment