Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Ranking a measure within a Matrix

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 d...
  • smpa01's avatar
    smpa01
    4 years ago

    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

     

     

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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"