Ranking a measure within a Matrix

[Anonymous]

Hello Power BI Community,

I am unable to come up with a way to rank individual products within a Matrix. Whenever I try and use RANKX or similar functions the rank is always 1 for all rows. The difficulty I am having is that I need the ranking to be dynamic. It should be dynamic so that the rank will change based on the selected slicers. My goal is to have the table show the desired data and rank the Revenue values from greatest to least within the Matrix drill through.

The point of ranking these values within the matrix is to eventually create a function that will filter out all products except the "Top 5 and Bottom 5" dynamically based on the slicer selections.

Attached is the Dataset and the Power BI Report I created:

Dataset: https://www.dropbox.com/scl/fi/7jv81iswwky2b76o01jen/Order-Report.xlsx?dl=0&rlkey=iy1h5isqe7krea6qrs...

Image of Current State of Report:

[smpa01]

@Anonymous  I see this. I am not sure which error you are referring to?

[Anonymous]

See what you are seeing now! Thank you sorry for the confusion!

[smpa01 · Accepted solution]

@Anonymous  you can write a measure like this; pbix is attached

Ranking = 
VAR _AscendingOrder =
    RANKX ( ALLSELECTED ( 'Order Report'[Product Name] ), [_revenue],, ASC )
VAR _DescendingOrder =
    RANKX ( ALLSELECTED ( 'Order Report'[Product Name] ), [_revenue],, DESC )
VAR _selection =
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'New-Slicer'[Category] ) = "Bottom10"
            || SELECTEDVALUE ( 'New-Slicer'[Category] ) = "Bottom20"
            || SELECTEDVALUE ( 'New-Slicer'[Category] ) = "Bottom5", _AscendingOrder,
        _DescendingOrder
    )
VAR _value =
    IF ( _selection <= SELECTEDVALUE ( 'New-Slicer'[Value] ), _selection )
RETURN
   _value

[Anonymous]

Thank you for submitting a response! While the function seems to work for ranking Top 5, Top 10, and Top 20 it fails when selecting Bottom 5, Bottom 10, and Bottom 20. Thank you for this answer and it seems to be very close to what I am looking for but there seems to be an error when ranking from the "Bottom"