cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Resolver I

## 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?

1 ACCEPTED SOLUTION
Community Support

Hi @CalebR ,

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 4
Community Support

Hi @CalebR ,

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.

Impactful Individual

hello @CalebR ,
create anew calculated column,

RANK = RANKX(fILTER('Table','Table'[PO #]=EARLIER('Table'[PO #])),'Table'[PO Date],,ASC,dENSE)
Community Champion

For fun only, a showcase of powerful Excel formula,

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Resolver I

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors