Forum Discussion

yellow43's avatar
yellow43
Icon for Helper I rankHelper I
3 years ago

Rank with multiple conditions/columns

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

 

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:

And desired result should be:

Thanks in advance!

 

rank

3 Replies

  • yellow43's avatar
    yellow43
    Icon for Helper I rankHelper I
    3 years ago

    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

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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