Forum Discussion
Ranking by Date with Grouping
Hello All,
I am new to PowerBI and have looked at a lot of articles similar to this, but have yet to find a solution that works for me.
I have a table that has purchase order numbers with their respective order dates as well as item codes that were on that purchase order.
I am trying to add a "ranking" column that will rank each row idividually by the date grouped by the item code & PO. So for example data like this:
| PO # | PO Date | Item Code |
| 5462 | 7/8/2022 | 58-1234 |
| 5462 | 9/10/2021 | 58-1234 |
| 5462 | 8/10/2022 | 58-1234 |
| 5462 | 4/20/2021 | 19-576 |
| 7890 | 12/20/2022 | 58-1234 |
| 7890 | 9/20/2022 | 58-1234 |
| 2323 | 7/14/2021 | 19-576 |
| 2323 | 9/12/2022 | 58-1234 |
| 2323 | 7/6/2020 | 19-576 |
| 4567 | 1/1/2023 | 58-1234 |
Turning into this:
| PO # | PO Date | Item Code | Rank |
| 5462 | 4/20/2021 | 19-576 | 1 |
| 5462 | 9/10/2021 | 58-1234 | 3 |
| 5462 | 7/8/2022 | 58-1234 | 2 |
| 5462 | 8/10/2022 | 58-1234 | 1 |
| 7890 | 9/20/2022 | 58-1234 | 2 |
| 7890 | 12/20/2022 | 58-1234 | 1 |
| 2323 | 7/6/2020 | 19-576 | 2 |
| 2323 | 7/14/2021 | 19-576 | 1 |
| 2323 | 9/12/2022 | 58-1234 | 1 |
| 4567 | 1/1/2023 | 58-1234 | 1 |
Essentially even if there is over 50+ entires for each PO# & part #, I want the 1,2, & 3 rankings to be the most recent dates/entries.
Does anyone know of a simple way to do this?
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create a measure with below dax formula
Measure = VAR cur_po = SELECTEDVALUE ( 'Table'[PO #] ) VAR cur_item_code = SELECTEDVALUE ( 'Table'[Item Code] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Item Code] = cur_item_code && 'Table'[PO #] = cur_po ) RETURN RANKX ( tmp, CALCULATE ( SELECTEDVALUE ( 'Table'[PO Date] ) ),, DESC, DENSE )3. add a table visual with fields and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
CNENFRNLCommunity Champion
For fun only, a showcase of powerful Excel formula,
So this doesnt give me an error or anything, but its still not exactly what I am needing. Once entering this it is just giving me a rank column with a 1 in every row in the table. I need it to rank by purchaseorderdate top 3 newest to oldest and grouping by ItemCode. Here is an image of my visual showing you what im seeing.
To add more context, the reason I am doing this is because our order people want to see the three most recent POs for each item code. Here is an example of what the finalized report looks like. Youll see the PO#s are different and the date is sorted from most recent to oldest (top 3 only).This isnt the full page, at the top it just shows the item code along with our vendor.
Thank you for your response!
Hi Anonymous ,
Please try below steps:
1. below is my test table
Table:
2. create a measure with below dax formula
Measure = VAR cur_po = SELECTEDVALUE ( 'Table'[PO #] ) VAR cur_item_code = SELECTEDVALUE ( 'Table'[Item Code] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[Item Code] = cur_item_code && 'Table'[PO #] = cur_po ) RETURN RANKX ( tmp, CALCULATE ( SELECTEDVALUE ( 'Table'[PO Date] ) ),, DESC, DENSE )3. add a table visual with fields and measure
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.