How to Rank Data not by month year.

[eddywong71]

My Dataset like below.

 Month Cust_ID Balance

  202101   a1       100

  202102   a1     200

   202203   a1     300

202203    a2     400

I am using AGGR(RANK(SUM{$} Balance)), Cust_ID). The expression is work fine when Month selected. 

However, when user don't select any month. The same CUST_ID sometime rank are missing.  I want the table include all month is rank by the balance.So That result like below 

Rank Month Cust_ID Balance

 4     202101   a1       100

 3     202102   a1     200

 2    202203   a1     300

 1    202203   a2     400

[Onkar10]

In Rank field use: AGGR(RANK(SUM(Balance)), Cust_ID,Month)

[eddywong71]

Hi Onkar10 ,

I try this expression before. However, this is like group by ID, Month.

The result will like this. 

Rank Month Cust_ID Balance

 4     202101   a1       100

 3     202102   a1     200

 1    20203   a1     300

 1    20204   a2     400

Let my reclarify i want the result like below

Rank Month Cust_ID Balance

 4     202101   a1       100

 3     202102   a1     200

 2    202203   a1     300

 1    202204   a2     400

[Onkar10]

= AGGR(RANK(SUM(Balance)), Month)  

This should give you result you want.

[eddywong71]

Hi Onkar10, would you try the data like 202203   a2     400. I dont know why my result rank still not accurate.

I think maybe Cust_ID appear more than one. But after month filter Cust_ID  will be distinct.

[Onkar10]

Same result with :202203   a2     400

[eddywong71 · Accepted solution]

Rank(Total Cust_ID)  solve my problem