Rank suppliers' scores based on multiple criteria

[taiyaki35]

Hi all,

I want to create a Rank column that ranks suppliers' scores based on multiple criteria ( the same category, quarter, and commodity). But I'm not sure how to handle the data and appreciate any help or advice. 

Here is the sample data:

Category	Quarter	Supplier	Commodity	Score
Quality	Q2FY19	Supplier A	AAA	22
Quality	Q2FY19	Supplier B	AAA	16
Quality	Q3FY19	Supplier A	AAA	22
Quality	Q3FY19	Supplier B	AAA	17
Quality	Q4FY19	Supplier A	AAA	20
Quality	Q4FY19	Supplier B	AAA	14
Service	Q2FY19	Supplier A	AAA	17
Service	Q2FY19	Supplier B	AAA	18
Service	Q3FY19	Supplier A	AAA	15
Service	Q3FY19	Supplier B	AAA	14
Service	Q4FY19	Supplier A	AAA	19
Service	Q4FY19	Supplier B	AAA	13
Quality	Q2FY19	Supplier A	BBB	19
Quality	Q2FY19	Supplier B	BBB	22
Quality	Q3FY19	Supplier A	BBB	22
Quality	Q3FY19	Supplier B	BBB	19
Quality	Q4FY19	Supplier A	BBB	22
Quality	Q4FY19	Supplier B	BBB	22
Service	Q2FY19	Supplier A	BBB	17
Service	Q2FY19	Supplier B	BBB	15
Service	Q3FY19	Supplier A	BBB	19
Service	Q3FY19	Supplier B	BBB	16
Service	Q4FY19	Supplier A	BBB	20
Service	Q4FY19	Supplier B	BBB	16
Quality	Q2FY19	Supplier D	CCC	24
Quality	Q2FY19	Supplier C	CCC	25
Quality	Q3FY19	Supplier D	CCC	24
Quality	Q3FY19	Supplier C	CCC	24
Quality	Q4FY19	Supplier D	CCC	23
Quality	Q4FY19	Supplier C	CCC	24
Service	Q2FY19	Supplier D	CCC	17
Service	Q2FY19	Supplier C	CCC	14
Service	Q3FY19	Supplier D	CCC	18
Service	Q3FY19	Supplier C	CCC	16
Service	Q4FY19	Supplier D	CCC	20
Service	Q4FY19	Supplier C	CCC	17
Cost	Q2FY19	Supplier A	AAA	14
Cost	Q2FY19	Supplier B	AAA	12
Cost	Q2FY19	Supplier A	BBB	15
Cost	Q2FY19	Supplier B	BBB	10
Cost	Q2FY19	Supplier D	CCC	12
Cost	Q2FY19	Supplier C	CCC	13
Cost	Q3FY19	Supplier A	AAA	11
Cost	Q3FY19	Supplier B	AAA	8
Cost	Q3FY19	Supplier A	BBB	9
Cost	Q3FY19	Supplier B	BBB	4
Cost	Q3FY19	Supplier D	CCC	14
Cost	Q3FY19	Supplier C	CCC	13
Cost	Q4FY19	Supplier A	AAA	10
Cost	Q4FY19	Supplier B	AAA	9
Cost	Q4FY19	Supplier A	BBB	8
Cost	Q4FY19	Supplier B	BBB	7
Cost	Q4FY19	Supplier D	CCC	17
Cost	Q4FY19	Supplier C	CCC	15

Thank you!

[v-yangliu-msft · Accepted solution]

Hi  @taiyaki35 ,

Here are the steps you can follow：

1. Create calculated column.

Rank =
RANKX(
    FILTER('Table',
    'Table'[Category]=EARLIER('Table'[Category])&&
    'Table'[Quarter]=EARLIER('Table'[Quarter])&&
    'Table'[Commodity]=EARLIER('Table'[Commodity])),
    [Score],,ASC)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Greg_Deckler]

@taiyaki35 See if this helps as it has quite a few different scenarios both using RANKX and not. https://community.powerbi.com/t5/Quick-Measures-Gallery/To-Bleep-with-RANKX/m-p/1042520#M452