RANKX AND ORDER

[bestevez]

Hello,

I have a question I'd like to raise so that I can resolve it. As you can see in the table I would like to add a column (RANKX ORDER LY) where appears the ranking of last year's products that had the same order as this year's products. 

The RANKX and RANKX LY columns work perfectly.

YEAR	PRODUCT	QTY	RANKX
2018	PEPSI	50	1
2018	COCA COLA	45	2
2018	ORANGE	30	3
2018	PEPSI MAX	25	4
2018	COCA COLA ZERO	10	5
			RANKX	RANKX LY	RANKX ORDER LY
2019	COCA COLA	55	1	2	PEPSI
2019	PEPSI MAX	40	2	4	COCA COLA
2019	PEPSI	35	3	1	ORANGE
2019	COCA COLA ZERO	30	4	5	PEPSI MAX
2019	ORANGE	28	5	3	COCA COLA ZERO

Thanks

[MartynRamsden]

Hi @bestevez 

Not sure if I'm over simplifying things but I think you could use the LOOKUPVALUE function as follows.

RANKX ORDER LY = LOOKUPVALUE ( 'Table'[PRODUCT], 'Table'[RANKX LY], 'Table'[RANKX] )

Best regards,

Martyn 

[danielkinch]

Hello.

I do have a suggestion, albeit not a particularly elogant solution...

To achieve the table shown I would suggest first create the RANKX formula in your data table:

RANKX =

RANKX(

    FILTER(

    data_table,data_table[YEAR]=EARLIER(data_table[YEAR])),

data_table[QTY])

You can then use this to create a calculated table as follows:

RankLY = SUMMARIZECOLUMNS(data_table[YEAR],data_table[PRODUCT],data_table[QTY],data_table[RANKX])

Back in your main data table you can then create two calculated columns which call back the desired values:

RANKX LY = LOOKUPVALUE(RankLY[RANKX],RankLY[YEAR],data_table[YEAR]-1,RankLY[PRODUCT],data_table[PRODUCT])

And.... 

RANKX ORDER LY = LOOKUPVALUE(RankLY[PRODUCT],RankLY[YEAR],data_table[YEAR]-1,RankLY[RANKX],data_table[RANKX])

Hope this maybe of some use to you 😊

[bestevez]

Thanks i try it.