Rank Multiple Measures

[dbeaton41]

Hi

I've searched the forums but can't find an answer to my particular question.

I'm trying to rank one column against the values in other columns as shown below. I want the rank of the column Test_1 based on columns Test_1 to Test_5 where Test_1 to Test_5 are measures. Does anyone know if this is possible?

Thanks

Year	Month	Test_1	Test_2	Test_3	Test_4	Test_5	Rank_Test_1
2016	March	6.098371	6.44627	6.837873	5.820597	6.407869	4
2016	April	6.200762	6.5536	6.873662	6.003421	6.471136	4
2016	May	6.431377	6.465101	6.847985	5.908099	6.082241	3
2016	June	6.034497	6.587486	6.919705	6.221608	6.479582	5

[Anonymous · Accepted solution]

@dbeaton41 -

One possibility:

aTest = 
var vTest_1 = [Test_1]
return 1 
    + IF([vTest_1]<[Test_2],1,0) 
    + IF([vTest_1]<[Test_3],1,0) 
    + IF([vTest_1]<[Test_4],1,0) 
    + IF([vTest_1]<[Test_5],1,0)

Cheers!

Nathan

[Zubair_Muhammad · Accepted solution]

@dbeaton41 

Another way could be

Measure =
VAR temp = { [Test_1], [Test_2], [Test_3], [Test_4], [Test_5] }
RETURN
    COUNTROWS ( FILTER ( temp, [Test_1] <= [Value] ) )

[bns7]

Hi, I had a simillar issue, but I wanted the Rank for all the values i.e. Test_1 to Test_5 in the above example. how to go about.

Thanks!  

[dbeaton41]

Thanks Zubair! That also works.

[Anonymous · Accepted solution]

@dbeaton41 -

One possibility:

aTest = 
var vTest_1 = [Test_1]
return 1 
    + IF([vTest_1]<[Test_2],1,0) 
    + IF([vTest_1]<[Test_3],1,0) 
    + IF([vTest_1]<[Test_4],1,0) 
    + IF([vTest_1]<[Test_5],1,0)

Cheers!

Nathan

[dbeaton41]

Thanks Nathan ! That works perfectly.