Rank Closest to 100%

[sspringer]

I'm trying to figure out a way to rank based on a single expression that calculates a % and have it return rank = 1 to those that are closest to 100%.

Here is a use case.  I have 3 employees that all budgeted an amount for expenses.  I want to rank them based on how accurate they were to their projected values.

A = 105% to budget

B = 100% to budget

C = 98% to budget

I would like the rank to display:

B, 1

C, 2

A, 3

Can anyone help?

[sunny_talwar · Accepted solution]

May be like this:

Rank(-fabs(Expression - 1))

[sspringer]

This worked very well for one of my expressions but not the other (and they are almost identical).  Probably issue on my end.  Thank you!

[vishsaggi]

Can you explain this sunny ? I know what fabs do. What does expr - 1 ?

[sunny_talwar]

Here is the breakup of the expression:

Step 1 is to get the data standardized around 100% or 1 to get this to rank 1.

Step 2 is to find its absolute value

Step 3 is to make it negative so that 0 can be assigned rank 1

Step 4 is to calculate the rank

[vishsaggi]

Thanks very much mate. Appreciate that. Got it.