topic Re: Rank with multiple conditions/columns in DAX Commands and Tips

Rank with multiple conditions/columns

yellow43 — Thu, 30 Mar 2023 14:49:47 GMT

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!

Re: Rank with multiple conditions/columns

Greg_Deckler — Thu, 30 Mar 2023 14:52:35 GMT

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

Re: Rank with multiple conditions/columns

yellow43 — Thu, 30 Mar 2023 15:12:43 GMT

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

Re: Rank with multiple conditions/columns

Greg_Deckler — Thu, 30 Mar 2023 16:16:55 GMT

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