Top 20 based on measure value

[ks20]

Hi All,

I need Top 20 records based on measure value in table view.
1 dimension and 3 measures.
Dimension: Customer

Measure 1: Calls

Measure 2: Total Calls

Measure 3: Conversion %

Need top 20 based on Measure 3, though I got top 20 using this formula

If(Rank(measure, 1, 0) <= 20,measure)
Using this formula I get top 20 but I also get null values for rest of the records, how do I remove thoes nulls values and only showcase top 20 records.

Thank you in advance!

[vinieme12 · Accepted solution]

Use a Calculated Dimension  , move the condition from measure to dimension to restrict top 20 customers

Dimension

Customer 

 = Aggr (  If(Rank(measure, 1, 0) <= 20, Customer) , Customer)

  << Uncheck Include Null Values for this Dimension

Measure

Calls

Total Calls

Conversion %

[ks20]

Hi Vineeth, 

Thanks for your response, it worked perfectly!

[ks20]

Hey Vineeth,

I was trying the same for bottom 20 records, where I used
 = Aggr (  If(Rank(measure, 1, 0) >= 20, Customer) , Customer)

But I am getting more than 20 records. Kindly correct me if I am using wrong measure.
Thank you!

[vinieme12 · Accepted solution]

reverse the ranking using negative values

 = Aggr (  If(Rank(   -(measure), 1, 0) <= 20, Customer) , Customer)

[ks20]

Thank you once again. 

[ks20]

Hello again @vinieme12 , 

In the following scenario, for top 20 and bottom 20 values, if the rank is same for last 4 records, it showing all the 4 records with rank 20. Whereas, it should only show top 20 records, with respect to max measure value.
Any suggestion on the same? Thank you

[vinieme12]

do a weighted rank based on multiple measures

[ks20]

Can you explain it further?