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
Super User

@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!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!