Rank based on two columns and should not change when filter is applied

[Braveen]

Hi,

I am trying to create a rank based on Zone, ID and Metric 1 which should not change when ID filter is applied.

Like say I filter for  4, Rank should not change.

I have created the rank using below expression;

aggr(rank(sum([Metric 1])), Zone, ID)

But, rank is recalculated based on the filtered values. Would appreciate if you could share your inputs.

- Braveen

[Chanty4u]

Try this 

Rank(Total sum([Metric 1]), Zone, ID)

[Braveen]

@Chanty4u Thanks for the suggestion. This expression ranks the ID but disregarding the Zone (Rank starts as 1 and ends at 6) , also when I apply filter, rank is recalculating.

I need the rank of the ID within the zone based on the Metric 1 and should not change when I apply the ID filter. 

[Kushal_Chawda]

@Braveen  Just exclude the selections of your dimensions you have used in aggr function

aggr(rank(sum({<Zone=, ID=>}[Metric 1])), Zone, ID)

[Braveen]

@Kushal_Chawda It is broadly grouped like 1-20 or 1-10 as the rank for all 10/20 records. 

[Kushal_Chawda]

@Braveen  don't understand this statement. Basically in my previous post I have provided you solution to not change the Rank based on selection

[Braveen]

@Kushal_Chawda   This is how it looks

[Kushal_Chawda]

@Braveen  Your Ranking will be based on State so you need to include state in Aggr instead ID. If you add more dimension in table eg. ID..exclide the selection of that as well in Set(don't include it in aggr)

aggr(rank(sum({<Zone=, State=>}[Metric 1])), Zone,State)