cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
taiyaki35
Microsoft
Microsoft

Rank suppliers' scores based on multiple criteria

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:

CategoryQuarterSupplierCommodityScore
QualityQ2FY19Supplier AAAA22
QualityQ2FY19Supplier BAAA16
QualityQ3FY19Supplier AAAA22
QualityQ3FY19Supplier BAAA17
QualityQ4FY19Supplier AAAA20
QualityQ4FY19Supplier BAAA14
ServiceQ2FY19Supplier AAAA17
ServiceQ2FY19Supplier BAAA18
ServiceQ3FY19Supplier AAAA15
ServiceQ3FY19Supplier BAAA14
ServiceQ4FY19Supplier AAAA19
ServiceQ4FY19Supplier BAAA13
QualityQ2FY19Supplier ABBB19
QualityQ2FY19Supplier BBBB22
QualityQ3FY19Supplier ABBB22
QualityQ3FY19Supplier BBBB19
QualityQ4FY19Supplier ABBB22
QualityQ4FY19Supplier BBBB22
ServiceQ2FY19Supplier ABBB17
ServiceQ2FY19Supplier BBBB15
ServiceQ3FY19Supplier ABBB19
ServiceQ3FY19Supplier BBBB16
ServiceQ4FY19Supplier ABBB20
ServiceQ4FY19Supplier BBBB16
QualityQ2FY19Supplier DCCC24
QualityQ2FY19Supplier CCCC25
QualityQ3FY19Supplier DCCC24
QualityQ3FY19Supplier CCCC24
QualityQ4FY19Supplier DCCC23
QualityQ4FY19Supplier CCCC24
ServiceQ2FY19Supplier DCCC17
ServiceQ2FY19Supplier CCCC14
ServiceQ3FY19Supplier DCCC18
ServiceQ3FY19Supplier CCCC16
ServiceQ4FY19Supplier DCCC20
ServiceQ4FY19Supplier CCCC17
CostQ2FY19Supplier AAAA14
CostQ2FY19Supplier BAAA12
CostQ2FY19Supplier ABBB15
CostQ2FY19Supplier BBBB10
CostQ2FY19Supplier DCCC12
CostQ2FY19Supplier CCCC13
CostQ3FY19Supplier AAAA11
CostQ3FY19Supplier BAAA8
CostQ3FY19Supplier ABBB9
CostQ3FY19Supplier BBBB4
CostQ3FY19Supplier DCCC14
CostQ3FY19Supplier CCCC13
CostQ4FY19Supplier AAAA10
CostQ4FY19Supplier BAAA9
CostQ4FY19Supplier ABBB8
CostQ4FY19Supplier BBBB7
CostQ4FY19Supplier DCCC17
CostQ4FY19Supplier CCCC15

 

Thank you!

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1627608355421.png

 

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

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1627608355421.png

 

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

View solution in original post

Greg_Deckler
Super User IV
Super User IV

@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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors