Create Rank column based on grouping and order by another column

[Anonymous]

I need to create a calculated column which will contain a rank value but this will be based on multiple columns: The sample data is as below : I need to create a Rank column and its shoulbe grouped by statment_id and ordered by Line_id.

Example: For statement_id = 400, line_id 321 should get Rank 1, line_id = 303 should get Rank 2 For statement_id = 100, line_id 222 should get Rank 1, line_id = 221 should get Rank 2

Sample Data: 

statement_id	line_id	value_date	Actv_Flag
400	321	5/15/2019	Y
400	321	5/15/2019	Y
400	303	5/13/2019	Y
400	303	5/13/2019	Y
100	222	4/9/2019	Y
100	222	4/9/2019	Y
100	221	4/9/2019	Y
100	221	4/9/2019	Y
100	202	4/1/2019	Y
100	202	4/1/2019	Y
608	553	4/10/2018	Y
608	553	4/10/2018	Y
608	552	6/26/2019	Y
608	552	6/26/2019	Y
608	551	6/26/2019	Y
608	551	6/26/2019	Y

Expected Result:

statement_id	line_id	value_date	Actv_Flag	Rank
400	321	5/15/2019	Y	1
400	321	5/15/2019	Y	1
400	303	5/13/2019	Y	2
400	303	5/13/2019	Y	2
100	222	4/9/2019	Y	1
100	222	4/9/2019	Y	1
100	221	4/9/2019	Y	2
100	221	4/9/2019	Y	2
100	202	4/1/2019	Y	3
100	202	4/1/2019	Y	3
608	553	4/10/2018	Y	1
608	553	4/10/2018	Y	1
608	552	6/26/2019	Y	2
608	552	6/26/2019	Y	2
608	551	6/26/2019	Y	3
608	551	6/26/2019	Y	3

Thanks in advance 🙂

[MFelix · Accepted solution]

Hi @Anonymous ,

Create the following calculated column to your table:

Rankin =
RANKX (
    FILTER ( 'Table'; 'Table'[statement_id] = EARLIER ( 'Table'[statement_id] ) );
    'Table'[line_id];
    ;
    DESC;
    DENSE
)

[harshnathani · Accepted solution]

Hi @Anonymous ,

incase you wan to create a Calculated COlumn

RANKing Group = RANKX(FILTER('Table','Table'[statement_id] = EARLIER('Table'[statement_id])),'Table'[line_id],,DESC,DENSE)

Incase you want to create a measure

RANKing OrderId = RANKX(FILTER(ALL('Table'[statement_id],'Table'[line_id]), 'Table'[statement_id] = MAX('Table'[statement_id])),CALCULATE(SUM('Table'[line_id])))

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Syndicate_Admin]

Hello how about it, how could you apply the calculated column you showed, but for more than one parameter? That is, I apply a RANK but according to four other columns (for example in my case YEAR, QUARTER, AGGLOMERATE and INCOME, and not just a column like the detailed one.

I thank you in advance! I can't find this solution anywhere.

[MFelix · Accepted solution]

Hi @Anonymous ,

Create the following calculated column to your table:

Rankin =
RANKX (
    FILTER ( 'Table'; 'Table'[statement_id] = EARLIER ( 'Table'[statement_id] ) );
    'Table'[line_id];
    ;
    DESC;
    DENSE
)