Top 5 Based on Two measures

[#1]

Hi All,

I am facing issues in finding top 5 suppliers. In table I am calculating % which is first measure, But for some of the suppliers if top % is same then dimension limits picks up random suppliers. For eg. I have selected Top 5 from dimension limit, but if there are 10 Supplier  with same % , out of 10, it picks up randomly 5 values in Top 5.  Now requirement is, first criteria should be % and second criteria should be number of deliveries, So Out of 10 supplier with same %, Top5 should be highest % with highest number of deliveries.

let's say below data

Supplier	%	Deliveries
AA	100%	66
AB	100%	99
AC	100%	78
AD	100%	97
AF	100%	77
BC	100%	69
KC	100%	98
JK	100%	96
PT	100%	67
KT	100%	51

Note - % and Delivery is calculated

For above data Top 5 should be

OutPut Top 5
Supplier	%
AB	100%
KC	100%
AD	100%
JK	100%
AC	100%

[sunny_talwar · Accepted solution]

May be use Rank function

If(Rank(Expression% + ExpressionDeliveries/1E10) < 6, Expression%)

and

If(Rank(Expression% + ExpressionDeliveries/1E10) < 6, ExpressionDeliveries)

[#3]

Hi sunny,

Sometimes for lowest % , deliveries are high, so above expression is giving wrong results

[sunny_talwar]

I guess which one is the priority? % or deliveries? One of them need to take precedence over the other, right?

[#5]

first priority is % and then number

[sunny_talwar]

Then I don't see any reason why the above won't work .

Would you be able to share a sample where this isn't working

[#7]

Hi sunny,

ExpressionDeliveries/1E10..  what is /1E10?

[sunny_talwar]

Another way to say 1/10000000000

[#9]

How this works? can you give one example?

[sunny_talwar]

Its just Scientific notation‌

I guess an example would be 50000000000/1E10 vs. 50000000000/10000000000 should give you 5 in both the cases