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

[CJMolin]

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:

ID	ID2	AmountApproved	ScoringVariable
2005	200501	1000	1
2005	200502	2000	2
2005	200503	3000	3
2005	200504	4000	4
2005	200505	5000	5
2005	200506	6000	6
2005	200507	7000	7
2005	200508	8000	8
2005	200509	10000	9

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

AppNo	ProductAppNo	AmountDocs	ScoringVariable (calculated column)
2005	200501	10000	2
2005	200502	10000	2
2005	200503	10000	2
2005	200504	9000	1
2005	200505	15000	3
2005	200506	10000	2
2005	200507	10000	2
2005	200508	10000	2
2005	200509	10000	2

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

Wanted result!:

ID	ID2	AmountApproved	ScoringVariable	Calculation
2005	200501	10000	4	TIE between (1+2+3+4+5+6+7) / 7
2005	200502	10000	4	TIE between (1+2+3+4+5+6+7) / 7
2005	200503	10000	4	TIE between (1+2+3+4+5+6+7) / 7
2005	200504	14000	8	Distinct place 8
2005	200505	15000	9	Distinct place 9
2005	200506	10000	4	TIE between (1+2+3+4+5+6+7) / 7
2005	200507	10000	4	TIE between (1+2+3+4+5+6+7) / 7
2005	200508	10000	4	TIE between (1+2+3+4+5+6+7) / 7
2005	200509	10000	4	TIE 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

[CJMolin]

Solved it myself.

[v-yulgu-msft]

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

[CJMolin]

Any experts out there? 🙂