How to get top five with the highest rank?

[kristeljoymalapitan]

Good Day,

How can I get the top 5 Region with the highest outstanding balance percentage per month in a line graph, please see below sample screenshot

I use this measure  : SUM({<[CLASSIFICATION] = {'PD','LEGAL'}>}[outstanding_bal])/(SUM({<[CLASSIFICATION = {'PD','LEGAL','REGULAR'}]>}[outstanding_bal])

Thank you

[sunny_talwar]

So the Region can be different top 5 in months? or do you want to show the same top 5 regions across all months?

[asinha1991]

did you try dimension limit tab in property and select first expression top 5?

if you have to do it at dimension level for some reason, use this in dimension

aggr(if(rank(SUM({<[CLASSIFICATION] = {'PD','LEGAL'}>}[outstanding_bal])/(SUM({<[CLASSIFICATION = {'PD','LEGAL','REGULAR'}]>}[outstanding_bal]))<5,Region),Region)

[kristeljoymalapitan]

I need to get the top 5 region per month.so it could be different regions top 5 per month

[deepakahirwar]

Top 5 Values:

SUM({<Product = {"=Rank(SUM({<Type = {'Product'}>}Value),4)<=5"}>}Value)

Bottom 5:

SUM({<Product = {"=Rank(-SUM({<Type = {'Product'}>}Value),4)<=5"}>}Value)

[kristeljoymalapitan]

this is not working due to error in expression.

[Surya]

Hello,

As per my understanding .

u have 2 dimesions..

goto properties > dimension limits >select 2nd dimension > show >only largest 5

I think it will work

[sunny_talwar · Accepted solution]

May be this

Aggr(

If(Rank(Sum({<[CLASSIFICATION] = {'PD', 'LEGAL'}>} [outstanding_bal])/Sum({<[CLASSIFICATION] = {'PD', 'LEGAL', 'REGULAR'}>} [outstanding_bal])) < 6, Sum({<[CLASSIFICATION] = {'PD', 'LEGAL'}>} [outstanding_bal])/Sum({<[CLASSIFICATION] = {'PD', 'LEGAL', 'REGULAR'}>} [outstanding_bal]))

, MONTHYEAR, REGION)

[kristeljoymalapitan]

Hi Surya,

It works, however how can I assure that I got the highest 5 rank?

What expression to be use?

Thank you

[kristeljoymalapitan]

Thanks Sunny_talwar, it works! 🙂