Next Top 5 Ranked

[PowerBIFan5]

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!

[v-heq-msft]

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]

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?

[v-heq-msft · Accepted solution]

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

[PowerBIFan5]

Hi Albert, Thank you for taking the time to explain - makes sense. I needed something that would be dynamic since the selected item rank will change depending on what's selected.  I ended up solving the issue using a duplicate fact table.

[Ashish_Mathur]

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]

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

[lbendlin]

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]

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.