Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
CalebR
Resolver I
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 DateItem Code
54627/8/202258-1234
54629/10/202158-1234
54628/10/202258-1234
54624/20/202119-576
789012/20/202258-1234
78909/20/202258-1234
23237/14/202119-576
23239/12/202258-1234
23237/6/202019-576
45671/1/202358-1234

 

 

Turning into this: 

PO #PO DateItem CodeRank
54624/20/202119-5761
54629/10/202158-12343
54627/8/202258-12342
54628/10/202258-12341
78909/20/202258-12342
789012/20/202258-12341
23237/6/202019-5762
23237/14/202119-5761
23239/12/202258-12341
45671/1/202358-12341

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
v-binbinyu-msft
Community Support
Community Support

Hi @CalebR ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1674106879620.png

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

vbinbinyumsft_1-1674106975543.png

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.

View solution in original post

4 REPLIES 4
v-binbinyu-msft
Community Support
Community Support

Hi @CalebR ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1674106879620.png

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

vbinbinyumsft_1-1674106975543.png

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.

eliasayy
Impactful Individual
Impactful Individual

hello @CalebR ,
create anew calculated column,

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

CNENFRNL_0-1674075694324.png

 

For fun only, a showcase of powerful Excel formula,

CNENFRNL_1-1674075780246.png


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!

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.

CalebR_0-1674076587826.png
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). 

CalebR_2-1674077098757.png

This isnt the full page, at the top it just shows the item code along with our vendor.

Thank you for your response!

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors