Filtering Values displayed in Bar Chart based on a range of ranks

[tomrollinson]

Hi,

I am relatively new to Power BI so would appreciate any help with the below.

I am wanting to be able to limit the number of values returned in a bar chart depending on the rank of a selected value - showing the selected value, and the two values above & two values below. I attempted to create two measures, that returns the rank number two above and below, and thought I would be able to apply these as a filter range in the visual - but this does not work.

This could be applied to a footfall league table. If there were 20 teams, I am aiming to create a bar chart that allows to select a specific team, and also return the two teams above and below, based on league position. The bar chart would look something similar to the below, to allow to place the selected team in context - if selected Tottenham for example.

It would be great to understand how/if this could be achieved - as I have another use case where I would like to do the same, but applying to a list of ~2,000 places (so understanding similar ranked places would be useful).

Thanks in advance.

[v-huijiey-msft · Accepted solution]

Hi @tomrollinson ,

First, create an unjoined table and drag the League Position column of this table into the slicer:

Table 2 = DISTINCT('Table'[League Position])

Create three measures:

Total Points = SUM('Table'[Points])

Rank = RANKX(ALL('Table'[League Position]),[Total Points],,DESC,Dense)

Measure = 
VAR __selected_value = SELECTEDVALUE('Table 2'[League Position])
VAR __ranking = CALCULATE([Rank],'Table'[League Position]=__selected_value)
VAR __bef = __ranking - 2
VAR __aft = __ranking + 2
VAR _cur_ranking = [Rank]
VAR _result = IF(ISBLANK(__selected_value)|| (_cur_ranking>=__bef && _cur_ranking<=__aft),1)
RETURN
_result

Finally, put the measure in a visual object-level Filter to filter the data to be displayed only if the measure is 1:

The page looks like this:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[tomrollinson]

Thanks @v-huijiey-msft - I have worked through your suggestions and it is has got me 95% of the way there for what I need. I have tried altering the above but I am struggling to get what I require. Is there a way to modify the above so that you can have the team name in a slicer, rather than needing the rank in the slicer?

Thanks

[v-huijiey-msft]

Hi @tomrollinson ,

In the same way, you create a separate unjoined table for the team name inside the slicer.

Something like this:

Table 2 = DISTINCT('YourTable'[team name])

The logic behind this remains the same.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[tomrollinson]

Thank you @v-huijiey-msft - I have just had another look using your suggestions above and have got it to work as I needed. Thanks for your support.