Rank filtered values

[mbidelski]

Hello, Im trying to create a set of charts like this:

The year slicer filters data for the left chart. The right chart uses a copy of years column and measure ignoring the year filter from the slicer :

ValForChart = CALCULATE(SUM(IPBI[Value]);ALL('Period Map'[FY]))

I'm using the Top N filter to show just the companies that had the highest values in the given year.

But what I really want is to show top 5 AND bottom 5 companies by Value in the filtered year - any idea how to achieve this?

ADDITIONAL INFO:

here is the RankX that @amitchandak suggested

Rank = RANKX(ALL(IPBI);[ValForChart#];;DESC;Dense)

And here is what happens:

Every value gets a rank, for each year. I want to show 5 companies, that were highest (and lowest )by Value in a given year. Not just top values out every year and every company.

I think a solution would look like this:

 

So now you can see the ranks are the way I need them, but I can only do the SOLUTION measure with a static expression:

SOLUTION = CALCULATE(SUM(IPBI[Value]);IPBI[FY2]="2017")

Hope its clear now. I spent an ungodly amount of time trying to figure this out, and it seems it should be doable, but I am very new to DAX and databases in general. Thank you for any suggestions!

UPDATE2

Why RANKX with ALLSELECTED does not work:

Only ranks the values from the filtered year, everything else is ranked as the last value. Doesn't work will ALL either, screenshot is above.

UPDATE3

Doesn't work with SELECTEDVALUE, Perdio Map[FY] is the name of the column that is filtered by the slicer. Should I reference it differently?

[Vera_33]

oh, sorry...can you try it if you only do single selection in the slcier:

SOLUTION =
VAR CurYear = SELECTEDVALUE('Period Map'[FY])
RETURN
CALCULATE(SUM(IPBI[Value]);IPBI[FY2]=CurYear)

[Anonymous]

In general, all the cells including both filtered values and unfiltered values will be ranked by the Rank function.
In the formula, A2 are the first cell you want to rank, A2:A8 is the range you want to rank.
Then when you filter data, the visible values will be automatically reranked.

[Vera_33]

Hi @mbidelski  just a little thought, if you use RANKX to rank all the values, you have the rank index numbers so you can use IF to blank out middle values and leave only top 5 and bottom 5.

[amitchandak]

@mbidelski 

If you want both top and Botton together then it bit tricky. Otherwise, have rank (Asc and desc two measures) and use it has a visual level filter

In case you want both then have like this

 ValForChart = CALCULATE(SUM(IPBI[Value]);ALL('Period Map'[FY]))

 rank asc = ranks(all(IPBI),[ValForChart],,asc,dense)
 rank desc = ranks(all(IPBI),[ValForChart],,desc,dense)
  ValForChart Ranked = calculate([ValForChart],filter(all(IPBI),[rank asc]<=5 || [rank desc ]<=5))

example of rank in visual level filter

https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[mbidelski]

Hi, I added a response in the original post - am able to upload images only to the original post it seems, and makes things much easier to explain.

[Vera_33]

Hi @mbidelski  it is because ALL removes all the filters, you can use ALLSELECTED

[mbidelski]

With ALLSELECTED only the values from the filtered year get ranked. The rest is ranked at the bottom (last position in the ranking). The chart only shows one year. Screen: https://imgur.com/koOUhiF

[Vera_33]

Hi @mbidelski, can't see your image, use ALL or ALLSELECTED depends on how you want to RANK, RANKX can compare different values with the 3rd argument as well. You can get your slicer year into your measure, so it is dynamtic.

SOLUTION = CALCULATE(SUM(IPBI[Value]);IPBI[FY2]=SELECTEDVALUE(yourSlicerYear))

Rank = RANKX(ALL(IPBI);[SOLUTION];;DESC;Dense)

[mbidelski]

@Vera_33 thank you for your help. Here's what happens with ALLSELECTED, the Rank function ignores everything thats filtered out, I need all years to get the same rank:

SELECTEDVALUE didnt work either, maybe I got the syntax wrong?