Forum Discussion

tomrollinson's avatar
tomrollinson
Frequent Visitor
2 years ago
Solved

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

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.

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      tomrollinson
      Frequent Visitor
      2 years ago

      Thanks Anonymous - 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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!