Forum Discussion

CJMolin's avatar
CJMolin
Icon for Helper II rankHelper II
8 years ago

Scoring a variable equally "weighted" (Not like the RANKX function)

Hello,

 

I've created a scoring model and everything worked out just fine. Until I realised I might have to score my variables differently. The score should be set on "AppNo"-level. Meaning the AmountDocs should be scored internally within each AppNo.

 

Today:

I have a table containing following information. If all ProductAppNo has different values in the AmountDocs field theres not a problem. It means that following result would occur:

 

IDID2AmountApprovedScoringVariable
200520050110001
200520050220002
200520050330003
200520050440004
200520050550005
200520050660006
200520050770007
200520050880008
2005200509100009

 

ALTHOUGH! When a TIE occurs, I receive following result:

 

AppNoProductAppNoAmountDocsScoringVariable (calculated column)
2005200501100002
2005200502100002
2005200503100002
200520050490001
2005200505150003
2005200506100002
2005200507100002
2005200508100002
2005200509100002

 

DAX used to calculate the ScoringVariable = =RANKX(FILTER(DF;[AppNo]=EARLIER([AppNo]));[AmountDocs];;DESC;Dense)

 

Wanted result!:

 

IDID2AmountApprovedScoringVariableCalculation
2005200501100004TIE between (1+2+3+4+5+6+7) / 7
2005200502100004TIE between (1+2+3+4+5+6+7) / 7
2005200503100004TIE between (1+2+3+4+5+6+7) / 7
2005200504140008Distinct place 8
2005200505150009Distinct place 9
2005200506100004TIE between (1+2+3+4+5+6+7) / 7
2005200507100004TIE between (1+2+3+4+5+6+7) / 7
2005200508100004TIE between (1+2+3+4+5+6+7) / 7
2005200509100004TIE between (1+2+3+4+5+6+7) / 7

 

Need help with how I should write my DAX-calculated column to achieve above result in the "ScoringVariable" according to the calculation mentioned in the "Calculation" column. Thank you very much for your help!

 

CJ

Need Help

3 Replies

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Hi CJMolin,

       

      Glad to hear that you have solved it by yourself. If possible, would you please kindly share your solution and mark it as an answer so that it can benefit more users having similar problem?

       

      Regards,
      Yuliana Gu