Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 the...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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!