Getting the 2th largest number in a List of Measures

[Ratier]

I have 6 Master Itens classified as Measures, let's say A,B,C,D,E,F. Let's call them ListOfMeasures.

I want to establish a ranking order for each measure, let's say A>F>D>B>C>E.

My goal is to change the value of A to the value of E, change the value of F to the value of C and change the value of D to the value of B.

The logic I was aiming for was, for each Measure in ListOfMeasures

• If(Rank(ListOfMeasures)=1,Max(ListOfMeasures,6),
• If(Rank(ListOfMeasures)=2,Max(ListOfMeasures,5),
• If(Rank(ListOfMeasures)=3,Max(ListOfMeasures,4),
• If(Rank(ListOfMeasures)=4,Max(ListOfMeasures,3),
• If(Rank(ListOfMeasures)=5,Max(ListOfMeasures,2),
• If(Rank(ListOfMeasures)=6,Max(ListOfMeasures,1))))))

Things I tried:

• Rank() function can't rank a list of Measures, only a list of values of a given field;
• RangeMax function works, giving me the greatest value among A,B,C,D,E,F.  It'is limited though, since I can only obtain the 1th greatest value and not the 2th and so on.
• wildmatch(RangeMax(),,,,) gives me the position of the greatest. I can't use it for each case as the code would be gigantic and slow to process
• firstsortedvalue() has the same problem as Rank()

How can I sort this out?

[Kushal_Chawda · Accepted solution]

@Ratier  share sample data with expected output

[Ratier · Accepted solution]

Got It by myself but I thank you for the help

I created 6 variables:

• vA=Formula of Measure A
• vB=Formula of Measure B
• vC=Formula of Measure C
• vD=Formula of Measure D
• vE=Formula of Measure E
• vF=Formula of Measure F

Then, for exemple, to change the value of vB based on it's rank among A,B,C,D,E and F, I used:

• If('$(=$(vB))'=Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),1),Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),6),
• If('$(=$(vB))'=Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),2),Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),5),
• If('$(=$(vB))'=Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),3),Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),4),
• If('$(=$(vB))'=Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),4),Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),3),
• If('$(=$(vB))'=Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),5),Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),2),
• If('$(=$(vB))'=Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),6),Max(ValueList('$(=$(vA))','$(=$(vB))','$(=$(vC))','$(=$(vD))','$(=$(vE))','$(=$(vF))'),1)
• ))))))

Using the same formula for vA,vC,vD,vE and vF but changing the variable in red to the corresponding one, I can reverse the values as I intended:

• greatest value changing to the lowest value among the 6.
• 2th greatest value changing to the 2th lowest value among the 6.
• and so on...