Find rank based on multiple columns - calculate rank horizontally

[kinjal1645]

Hi All,

I want to find the rank of a particular column across other columns.

In the below chart, I am want to find the rank of Measure1 among all measures.

Dimension1	Dimension2	Mesaure1	Measure2	Measure3	Measure4	MeasureN	Rank of Measure1
A	A1	20	40	50	10	30	4
A	A2	60	100	3	10	30	2
B	B1	40	10	30	130	40	2
B	B2	200	130	50	140	60	1
C	C1	60	140	30	150	70	4
C	C2	90	150	40	50	80	2

Kindly help.

@sunny_talwar @tresesco @swuehl @Gysbert_Wassenaar 

Thanks

[sunny_talwar]

How exactly are you getting these ranks for Measure 1? Specifically, how are you getting 4 and repeating 2s

[Kushal_Chawda]

Would you like to do it in script or front end?

[kinjal1645]

Hi Kush,

Do you have a solution in script? otherwise front end will also do.

It is going to be the same measure for those fixed columns. 

[kinjal1645]

Hi Sunny,

If you see row 1, rank of measure 1 in measure1 to measureN is 4.

Row1:

Mesaure1 Measure2 Measure3 Measure4 MeasureN
20                 40               50                10                 30
4                     2                 1                   5                  3

Similarly, in row 5, rank of measure1 is 4.

Hope this helps.

[Brett_Bleess]

@sunny_talwar , @Kushal_Chawda Hey guys, suspect the notification issue messed up again on this post, just wanted to let you both know there is new info from the poster.

Cheers,
Brett

[Kushal_Chawda]

@kinjal1645  you can do something like below

For Measure 1  Rank, you need to compare  Measure 1 with all other measures and assign 1 value if it is less than the any other measure. So you will get 1 value for each comparison which you can add it to get the rank.

See the expression below for Measure 1 Rank

Here I have used sum(Measure1) & sum(Measure2) as example. You can replace it with actual measure 1 and measure 2 expressions

=rangesum(if(sum(Mesaure1)<sum(Measure2),1,0),
if(sum(Mesaure1)<sum(Measure3),1,0),
if(sum(Mesaure1)<sum(Measure4),1,0),
if(sum(Mesaure1)<sum(MeasureN),1,0),1)

Similarly , you can write the Measure 2 Rank expression as below

=rangesum(if(sum(Measure2)<sum(Mesaure1),1,0),
if(sum(Measure2)<sum(Measure3),1,0),
if(sum(Measure2)<sum(Measure4),1,0),
if(sum(Measure2)<sum(MeasureN),1,0),1)

Similarly you can write the expressions for each of the individual measure. Sorry I am bit lazy so not writing expressions for all measures, but I think you will get a fair idea with these examples