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 🙂
