Rank with 3 conditions

[Alex_192]

Hello Everyone,

I would like to look at my top 2 product per client per country.

To do that I have created a new table (see example below) with the information I need.

I would like to know if you could help me with the DAX formula to have the column rank 2016 & rank 2017 (which is for me a rankx based on 3 criteria: country, client and product name according to either 2016 sales or 2017 sales) ?

Country	Client	Product name	2016 sales	2017 sales	Rank 2016	Rank 2017	Final Rank 2016	Final Rank 2017
Italy	ITA_1	A	100	50	1	2	Top 2	Top 2
Italy	ITA_1	B	50	100	2	1	Top 2	Top 2
Italy	ITA_1	C	25	25	3	3	Others	Others
Italy	ITA_2	A	200	50	1	4	Top 2	Others
Italy	ITA_2	B	150	200	2	2	Top 2	Top 2
Italy	ITA_2	C	75	150	3	3	Others	Others
Italy	ITA_2	D	50	300	4	1	Others	Top 2
Germany	GER_1	A	30	50	1	1	Top 2	Top 2
Germany	GER_1	B	20	40	2	2	Top 2	Top 2
Germany	GER_1	C	10	10	3	3	Others	Others
Germany	GER_2	A	400	300	1	2	Top 2	Top 2
Germany	GER_2	B	350	200	2	3	Top 2	Others
Germany	GER_2	C	100	500	3	1	Others	Top 2

Thanks in advance,

Alex

[v-ljerr-msft · Accepted solution]

Hi @Alex_192,

Based on my test, you should be able to use the formulas below to create new calculate column in this scenario. 

Rank 2016 =
RANKX (
    FILTER (
        ALL ( Table1 ),
        Table1[Country] = EARLIER ( Table1[Country] )
            && Table1[Client] = EARLIER ( Table1[Client] )
    ),
    Table1[2016 sales]
)

Rank 2017 = 
RANKX (
    FILTER (
        ALL ( Table1 ),
        Table1[Country] = EARLIER ( Table1[Country] )
            && Table1[Client] = EARLIER ( Table1[Client] )
    ),
    Table1[2017 sales]
)

Note: Just replace 'Table1' with your real table name.

Regards

[Alex_192]

Hi v-ljerr-msft,

Thank you for your help !!

It's working 🙂

Best,

Alex