Rankx Vs Sort By Column

[Anonymous]

Hello all,

I have a dataset like that:

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

In my reports I want the markets in a special order. For that reason I created this table:

Market	Order
Market B	1
Market A	2
Market D	3
Market C	4

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:

	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

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 🙂

[mahoneypat · Accepted solution]

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.  Replace MarketSort with the actual name of that table.

MarketRank =
RANKX (
    ALLSELECTED ( MarketSort[Market], MarketSort[Order] ),
    CALCULATE ( SUM ( Markets[Sales] ) ),
    ,
    DESC
)

Pat

[Anonymous]

Thank you very much. It totally worked 👍