Forum Discussion
Rank Item in Rows, Location in Columns
Hello,
I am looking to get a rank for sales for by Item for each loction and a grand total rank in one matrix.
my dataset looks like this:
| Date | Location | Product | Units Sold |
| 8/1/2024 | Location 2 | Item 1 | 3.000 |
| 8/1/2024 | Location 1 | Item 1 | 2.000 |
| 8/1/2024 | Location 3 | Item 1 | 2.000 |
| 8/4/2024 | Location 2 | Item 2 | 2.000 |
| 8/3/2024 | Location 2 | Item 2 | 2.000 |
| 8/2/2024 | Location 2 | Item 2 | 2.000 |
| 8/4/2024 | Location 2 | Item 3 | 1.000 |
| 8/3/2024 | Location 3 | Item 3 | 1.000 |
| 8/3/2024 | Location 2 | Item 3 | 1.000 |
| 8/2/2024 | Location 1 | Item 3 | 1.000 |
I want a rank output into a matrix for the dates selected that ranks sales of an item for each location:
| Product | Location 1 | Location 2 | Location 3 | Total |
| Item 1 | 1 | 2 | 1 | 1 |
| Item 2 | 3 | 1 | 3 | 2 |
| Item 3 | 2 | 3 | 2 | 3 |
So Item 1 in this example is the number 1 seller at Location 1 and Location 3, number 2 at Location 2, and Number 1 Overall.
I know I can create a a rankx function for each location, I am trying to figure out if there is an easier or more straightforward solution.
Edit: rankx does work. my issue was I was pulling item name from a product table so there was no connection to location. Sorry for not fully clarifying my full dataset. Thank you for the solutions, as they are what pointed me to my mistake.
Hi Gryphon269 ,
You can use the following measure in VALUE in the matrix, which might accomplish what you need.Measure = RANKX( ALL('Table'[Product]),CALCULATE(SUM('Table'[Units Sold])))I would be honored if my answer can solve your problem, if you have further questions, you can contact me at any time, I will receive a message after the first time to reply!
Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
hello Gryphon269
i think rankx has already easy and straightforward (it only require one DAX).
Hope this will help.
Thank you.Hi Gryphon269 ,
You can use the following measure in VALUE in the matrix, which might accomplish what you need.Measure = RANKX( ALL('Table'[Product]),CALCULATE(SUM('Table'[Units Sold])))I would be honored if my answer can solve your problem, if you have further questions, you can contact me at any time, I will receive a message after the first time to reply!
Hope it helps!
Best regards,
Community Support Team_ Tom ShenIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I used these measures:
US = SUM(Data[Units Sold])Rank = RANK(DENSE,ALL(Data[Product]),orderby([US],DESC))Hope this helps.
