Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
yellow43
Helper I
Helper I

Rank with multiple conditions/columns

I have the below data and I need to have a ranking with multiple conditions:

 

2023-03-30_15h31_29.png

1.  same code, same country, different brand - first criteria is brand with highiest QTY

2. if there is a tie in QTY - 2nd criteria should be AVG SALES

3. QTY with 0 (zero) and AVG SALES with blank should not be considered

 

I have the following formula:

 

 

Rank measure: = 
IF (
    HASONEVALUE ( 'Table2'[country] ),
    CALCULATE (
        COUNTROWS ( 'Table2' ),
        WINDOW (
            1,
            ABS,
            4,
            REL,
            SUMMARIZE (
                ALL ( 'Table2'),
                'Table2'[country],
                'Table2'[brand],
                'Table2'[QTY], 'Table2'[AVG SALES]
                
            ),
            ORDERBY ( 'Table2'[QTY], ASC, 'Table2'[AVG SALES], ASC ),
            KEEP,
            PARTITIONBY ( 'Table2'[country])
        )
    )
)

 

 

But shows ranking like this:

yellow43_0-1680187449366.png

And desired result should be:

2023-03-30_15h45_02.png

Thanks in advance!

 

3 REPLIES 3
yellow43
Helper I
Helper I

@Greg_Deckleri have already tried:

Ranking 2 = 
RANKX(FILTER(ALL('Table2'),Table2[code]=MAX(Table2[code])&&Table2[country] = MAX(Table2[country]) && Table2[brand] = MAX(Table2[brand])),CALCULATE(SUM(Table2[QTY])),,ASC)

and it's not working

@yellow43 OK, post sample data as text in a table. I'm not hand-typing all that data. You might have better luck with this though:

Ranking 2 = 
  VAR __code = MAX('Table2'[code]
  VAR __country = MAX('Table2'[country])
  VAR __brand = MAX('Table2'[brand])
  VAR __Table = FILTER(ALL('Table2'),Table2[code]=__code && Table2[country] = __coutnry && Table2[brand] = __brand)
  VAR __Result = RANKX(__Table,CALCULATE(SUM(Table2[QTY])),,ASC)
RETURN
  __Result


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Community Champion
Community Champion

@yellow43 If you post sample data as text can be more specific. Why aren't you using RANKX?

To *Bleep* with RANKX! - Microsoft Power BI Community



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.