Show nearest top and bottom values around filtered option

[Karyna]

Hello! This is my first message as it seems that I've read so many solutions but nothing worked for me. Thank you in advance for your help!

I need to automate finding the nearest values around the chosen one based on another value. In practice, I see it as I have a slicer with Country values and a matrix with Country and Money values. When I pick on slicer Country1 - the matrix shows 10 other Countries that are above my picked one based on the amount of Money, and 10 Countries that are right under.

I've tried to play around with the filtering option "TopN" but it showed me the highest value of the array, but I needed it "around" my picked Country. Also if it's needed to clarify - those values are columns of the one table source, so must be connected as I understand.

Thank you!

[rajendraongole1]

Hi @Karyna - Selecting a country from a slicer displays the 10 countries with the nearest money values above and below the selected country, Below DAX measures and custom columns should works.

Create measure :

SelectedCountry =
SELECTEDVALUE('Table'[Country])

Create a measure to get money value of the selected country:

SelectedCountryMoney =
CALCULATE(
SUM('Table'[Money]),
'Table'[Country] = [SelectedCountry]
)

Create a measure to rank the countries based on their money values

CountryRank =
RANKX(
ALL('Table'),
CALCULATE(SUM('Table'[Money])),
,
ASC
)

create another measure to find the difference:

DifferenceFromSelected =
ABS([SelectedCountryMoney] - SUM('Table'[Money]))

Create another measure to find the top and below:

Top10AboveAndBelow =
VAR SelectedRank = [CountryRank]
VAR Top10Above =
TOPN(
10,
FILTER(
ALL('Table'),
[CountryRank] < SelectedRank
),
[CountryRank],
DESC
)
VAR Top10Below =
TOPN(
10,
FILTER(
ALL('Table'),
[CountryRank] > SelectedRank
),
[CountryRank],
ASC
)
RETURN
COUNTROWS(
INTERSECT(
VALUES('Table'[Country]),
UNION(Top10Above, Top10Below)
)
)

you can replace the Table and columns as per your existing tables

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Karyna]

Unfortunately doesn't work the way I need it.
This is what I get when nothing is applied to the slicer

And this is when I pick the country:

This is how I adjusted my measurements:
1)  SelectedCountry = SELECTEDVALUE('Table'[Country])
2) SelectedCountryMoney = CALCULATE(SUM('Table'[Money]), FILTER('Table', 'Table'[Country] = [SelectedCountry]))
3) CountryRank = RANKX(ALL('Table'[Country]), CALCULATE(SUM('Table'[Money])),,DESC)
4) SelectedCountryRank = CALCULATE([CountryRank],FILTER('Table', 'Table'[Country] = [SelectedCountry]))
5) Top10AboveAndBelowCountries =

VAR SelectedRank = [SelectedCountryRank]

VAR Top10Above =

TOPN(

    10,

    FILTER(

        ALL('Table'),

        [CountryRank] < SelectedRank

    ),

    [CountryRank],

    DESC

)

VAR Top10Below =

TOPN(

    10,

    FILTER(

        ALL('Table'),

        [CountryRank] > SelectedRank

    ),

    [CountryRank],

    ASC

)

VAR CombinedTop10 =

UNION(

    SELECTCOLUMNS(Top10Above, "Country", 'Table'[Country]),

    SELECTCOLUMNS(Top10Below, "Country", 'Table'[Country])

)

RETURN

CombinedTop10

I'm still searching for a solution, so will be happy to see more ideas or suggestions. Thank you!

[Karyna]

Thank you! I'm actively trying the solution now, but unfortunately, not everything goes smoothly.

The first error appeared on the step "Create a measure to get money value of the selected country: SelectedCountryMoney = ...". I got an error - "A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

So I had to adjust to in this way in order to move on:

SelectedCountryMoney = CALCULATE(SUM('Table'[Money]), 'Table'[Country] = 'Table'[Money]).

The next error appeared in the last step - "Each table argument of 'INTERSECT' must have the same number of columns."