RANKING based on two columns - Group & Subgroup

[sshokri89]

Hi all,

I need to create a ranking in a specific manner; below is a sample data:

I have created GROUP as a calculated column using below measure:

Group = "Dealer " & RANKX(ALL(TABLE),TABLE[National Group],,ASC,Dense)

now I need to create another Calculated column but that needs to follow GROUP. In other words, I need to RANK each row based on their TOTAL value. for example, first row > total: 1 is the lowest amongst 4 rows. So I want to rank it as 1.4. Then second row is highest of all for DEALER 1 so it's my 1.1. The same logic is applied for all.

Thanks all for your help in advance 🙂

[ALLUREAN · Accepted solution]

Hi, @sshokri89 

Please check this example:

https://www.dropbox.com/s/udyww9onx3sjvj2/RANKING%20based%20on%20two%20columns%28allure-analytics.co...

Group_Calculated = "Dealer " & RANKX(ALL('TABLE'),'TABLE'[National Group],,ASC,Dense)

SubGroup =

"Dealer " &

RANKX(FILTER('TABLE',

'TABLE'[National Group] = EARLIER('TABLE'[National Group])),

'TABLE'[Total],,DESC)

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/

[amitchandak]

@sshokri89 

Assume total is column , a new column

Group = "Dealer " & RANKX(filter(TABLE,TABLE[National Group] = earlier(TABLE[National Group]) ), [Total],,ASC,Dense)