Find the closest 3 items based on filter

[catrin_reach]

I have 60 schools, and I want users to be able to select a school and see a number of different factors like the proportion of boys, receiving free meals etc in one bar chart, and then I want another bar chart below it to show the 3 schools in the data set with the closest values to the chosen schools figure. This needs to update everytime the users picks a new school. Ideally there would also be a text box that contains the name of the school that appears most frequently in the top 3 boxes of all the measures. I am pretty new to PowerBI so grateful for any help. thanks. examples/data below. 

School Name	% Boys	% EAL	% FSM	% PP	% SEND
School 1	50%	37%	41%	42%	17%
School 2	44%	4%	46%	47%	19%
School 3	43%	20%	14%	15%	5%
School 4	45%	1%	33%	36%	8%
School 5	48%	48%	68%	69%	15%

Example of what I want to show

[ChenwuZhu_Gmail · Accepted solution]

Hi @catrin_reach ,

1 Create a Parameter table for field. (used to select the factors)

Field Parameters in Power BI - Microsoft Power BI Community

2 Create a slicer table for school. (used to selecte the school name)

3 Create measure named "if closest 3 items"

if closest 3 items =
VAR _s =
    SELECTEDVALUE ( Slicer[School Name] )
VAR _sfactors =
    MAX ( Parameter[Parameter] )
VAR _sr =
    CALCULATE (
        SWITCH (
            _sfactors,
            "% Boys", MAX ( 'Table'[% Boys] ),
            "% EAL", MAX ( 'Table'[% EAL] ),
            "% FSM", MAX ( 'Table'[% FSM] ),
            "% PP", MAX ( 'Table'[% PP] ),
            MAX ( 'Table'[% SEND] )
        ),
        FILTER ( ALL ( 'Table' ), [School Name] = _s )
    )
VAR _r =
    TOPN (
        3,
        FILTER ( ALLSELECTED ( 'Table' ), [School Name] <> _s ),
        ABS (
            SWITCH (
                _sfactors,
                "% Boys", [% Boys],
                "% EAL", [% EAL],
                "% FSM", [% FSM],
                "% PP", [% PP],
                [% SEND]
            ) - _sr
        ), ASC
    )
RETURN
    IF (
        CONTAINS ( _r, 'Table'[School Name], SELECTEDVALUE ( 'Table'[School Name] ) ),
        1,
        0
    )

Then put it in filter pane and set it show item which is 1.

Then result:

Pbix file in the end you can refer.

Find the closest 3 items based on filter.pbix

Best regards.

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