Rankx Vs Sort By Column

Question

Hello all,I have a dataset like that:MarketProduct ColorDateSalesMarket ARedJanuary 20&nbsp;$&nbsp;&nbsp;&nbsp; 9,880Market BBlueFebruary 20&nbsp;$&nbsp;&nbsp;&nbsp; 9,418Market CYellowMarch 20&nbsp;$&nbsp;&nbsp;&nbsp; 4,536Market DBlackApril 20&nbsp;$&nbsp;&nbsp;&nbsp; 3,027Market BRedFebruary 20&nbsp;$&nbsp;&nbsp;&nbsp; 4,203Market CBlueMarch 20&nbsp;$&nbsp;&nbsp;&nbsp; 2,303Market BYellowApril 20&nbsp;$&nbsp;&nbsp;&nbsp; 5,162Market CBlueMay 20&nbsp;$&nbsp;&nbsp;&nbsp; 3,519Market DYellowMarch 20&nbsp;$&nbsp;&nbsp;&nbsp; 5,336In my reports I want the markets in a special order. For that reason I created this table:MarketOrderMarket B1Market A2Market D3Market C4&nbsp;I've sorted "Market" column by "Order" column. And created one to many relationship between tables. Everything worked perfectly fine up to this point. But when I created a "rankx" formula, It didn't work right. I want a Matrix visual which looks like this:&nbsp;MARKET B&nbsp;MARKET A&nbsp;MARKET D&nbsp;MARKET C&nbsp;&nbsp;SalesRankSalesRankSalesRankSalesRankJanuary 20&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,7074&nbsp;$&nbsp;&nbsp;&nbsp; 5,0531&nbsp;$&nbsp;&nbsp;&nbsp; 2,7932&nbsp;$&nbsp;&nbsp;&nbsp; 2,6243February 20&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,3493&nbsp;$&nbsp;&nbsp;&nbsp; 3,4402&nbsp;$&nbsp;&nbsp;&nbsp; 5,0381&nbsp;$&nbsp;&nbsp;&nbsp; 2,9704March 20&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9,2691&nbsp;$&nbsp;&nbsp;&nbsp; 1,9114&nbsp;$&nbsp;&nbsp;&nbsp; 7,7723&nbsp;$&nbsp;&nbsp;&nbsp; 9,1232April 20&nbsp;$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,3234&nbsp;$&nbsp;&nbsp;&nbsp; 3,2823&nbsp;$&nbsp;&nbsp;&nbsp; 3,5802&nbsp;$&nbsp;&nbsp;&nbsp; 4,0961&nbsp;I wrote this formula but it didn't work with "sort by column".Rank = RANKX ( ALLSELECTED(Data[Markets]) , CALCULATE ( SUM(Data[Sales])), , DESC)Can anyone please help me? Any solution will be welcomed 🙂&nbsp;

mahoneypat · Accepted Answer

If you are using the Market column from your sort table and you have sorted that column by the Order column, you'll need an expression like this.&nbsp; Replace MarketSort with the actual name of that table.
&nbsp;
MarketRank&nbsp;=RANKX&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED&nbsp;(&nbsp;MarketSort[Market],&nbsp;MarketSort[Order]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;SUM&nbsp;(&nbsp;Markets[Sales]&nbsp;)&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;&nbsp;&nbsp;&nbsp;DESC)
Pat
&nbsp;

Forum Discussion

Rankx Vs Sort By Column

2 Replies

Related content

Sorting

Sort by another column

Column Chart sorting

RankX

Table Sort , sort order , comparer

Market	Product Color	Date	Sales
Market A	Red	January 20	$ 9,880
Market B	Blue	February 20	$ 9,418
Market C	Yellow	March 20	$ 4,536
Market D	Black	April 20	$ 3,027
Market B	Red	February 20	$ 4,203
Market C	Blue	March 20	$ 2,303
Market B	Yellow	April 20	$ 5,162
Market C	Blue	May 20	$ 3,519
Market D	Yellow	March 20	$ 5,336

	MARKET B		MARKET A		MARKET D		MARKET C
	Sales	Rank	Sales	Rank	Sales	Rank	Sales	Rank
January 20	$ 1,707	4	$ 5,053	1	$ 2,793	2	$ 2,624	3
February 20	$ 3,349	3	$ 3,440	2	$ 5,038	1	$ 2,970	4
March 20	$ 9,269	1	$ 1,911	4	$ 7,772	3	$ 9,123	2
April 20	$ 1,323	4	$ 3,282	3	$ 3,580	2	$ 4,096	1