Comparing two teams via similar rank

[Parosel]

Hi,

I have a flat table which has several thousand organisations each with prevalences for about ten different conditions. All the organisations also have a rank associated with them based on where they are in the country (this rank is in another table joined on org code) 

What I would like to do is for the end user to be able to pick an organisation in a slicer to show all that organisations prevalences and for the report to automatically present the prevalences of the organisation closest to it in the national rank.

Does anyone have any ideas?

Thanks in advance

[tamerj1 · Accepted solution]

Hi @Parosel 

You can create a disconnected table which a duplicate of the ranking table adding to it the organization name

Slicer Table =
ADDCOLUMNS (
    'Ranking Table',
    "Organization ", CALCULATE ( VALUES ( 'Organizations Table'[Organization] ) )
)

Then create your filter measure

Filter Measre =
SELECTEDVALUE ( 'Ranking Table'[Ranking] )
    - SELECTEDVALUE ( 'Slicer Table'[Ranking] )

The place this measure in filter pane and Filter Measre =
Select "greater than" and insert the value "-5"

then

select "And"

then

Select "less than" and insert the value "5"

you can change the value to increase or decrease the range size