topic Re: Rank based on two columns and should not change when filter is applied in App Development

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

Braveen — Mon, 22 May 2023 12:29:48 GMT

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

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

Chanty4u — Mon, 22 May 2023 18:36:21 GMT

Try this

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

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

Braveen — Tue, 23 May 2023 06:42:05 GMT

@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.

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

Kushal_Chawda — Tue, 23 May 2023 10:57:11 GMT

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

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

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

Braveen — Tue, 23 May 2023 17:04:06 GMT

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

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

Kushal_Chawda — Tue, 23 May 2023 20:12:10 GMT

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

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

Braveen — Wed, 24 May 2023 06:37:58 GMT

@Kushal_Chawda This is how it looks

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

Kushal_Chawda — Wed, 24 May 2023 08:49:27 GMT

@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)