Forum Discussion

PowerBIFan5's avatar
PowerBIFan5
Frequent Visitor
1 year ago
Solved

Next Top 5 Ranked

Hello, I am trying to be able to show the next top 5 ranked items based on the item selected in a slicer for the page. For example, if the selected item is ranked 6, I want to be able to show who is 7,8,9,10,11. This is using a direct query as a data source so data transformation is limited. I can request to add tables if needed. Ideally, in the end, I will be able to have a card that would show item 7, and then another card that would show item 8, etc. I want to also be able to show associated metrics of these next ranked items. Thank you!

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi PowerBIFan5 ,
    As per your earlier post, you need to use the slicer as the basis for your selection, and if you don't want to enter it manually you can use hardcoding to specify the range of rankings you need to display.

    Top5 = 
IF(
    Table_1[Rank] > 6 && Table_1[Rank] <= 11,
        1,
        0
)

    Similar to the 6, and 11 here.

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi  PowerBIFan5  ,
    You can try the following steps:
    Sample data


    Create measure

    Rank = RANKX(ALL(Table_1),CALCULATE(SUM(Table_1[Value])),,ASC,Dense)

    Create a calculate table(There are thirteen pieces of test data above, if you have duplicates, based on your largest
    RANK value)

    SlicerTable = GENERATESERIES(1, 13, 1)

    Create measures

    Top5 = 
IF(
    Table_1[Rank] > SELECTEDVALUE(SlicerTable[Value]) && Table_1[Rank] <= SELECTEDVALUE(SlicerTable[Value]) + 5,
        1,
        0
)
    Rank+1 = 
CALCULATE(
    MAX(Table_1[Nmae]),
    FILTER(
        Table_1,
        Table_1[Rank] = SELECTEDVALUE(SlicerTable[Value]) +1
    )
)
    Rank+2 = 
CALCULATE(
    MAX(Table_1[Nmae]),
    FILTER(
        Table_1,
        Table_1[Rank] = SELECTEDVALUE(SlicerTable[Value]) +2
    )
)

    Apply the Top5 to table filter 


    Final output 

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

     

     

     

     

    • PowerBIFan5's avatar
      PowerBIFan5
      Frequent Visitor
      1 year ago

      Hi Albert, Thank you for taking the time to send over this method! Would I always have to input the value into the slicer manually or is there a way to make the slicer value dynamic based on the selected item's rank?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi PowerBIFan5 ,
        As per your earlier post, you need to use the slicer as the basis for your selection, and if you don't want to enter it manually you can use hardcoding to specify the range of rankings you need to display.

        Top5 = 
IF(
    Table_1[Rank] > 6 && Table_1[Rank] <= 11,
        1,
        0
)

        Similar to the 6, and 11 here.

        Best regards,
        Albert He

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

         

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Why not just list all of them in a table with their rank as a column?  Let the report user's eyes do the work.

    • PowerBIFan5's avatar
      PowerBIFan5
      Frequent Visitor
      1 year ago

      Thank you for the idea. I kept that in mind for a backup but I ended up solving the issue so I could display it as requested.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    I may be able to generate a table (not different card visuals).  I would like to try.  Share some data and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

    • PowerBIFan5's avatar
      PowerBIFan5
      Frequent Visitor
      1 year ago

      Thanks Ashish, I ended up solving the issue using a duplicate fact table so I am all set now.