cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mbidelski
Frequent Visitor

Rank filtered values

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

 

Adnotacja 2020-04-09 070352.png

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:
Adnotacja 2020-04-09 180635.png

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:

Adnotacja 2020-04-09 185507.png 

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:

Adnotacja 2020-04-10 202742.png

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?

Adnotacja 2020-04-13 154401.png

 

9 REPLIES 9
Super User IV
Super User IV

Re: Rank filtered values

@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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


Vera_33 Resolver I
Resolver I

Re: Rank filtered values

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.

mbidelski
Frequent Visitor

Re: Rank filtered values

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 Resolver I
Resolver I

Re: Rank filtered values

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

mbidelski
Frequent Visitor

Re: Rank filtered values

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 Resolver I
Resolver I

Re: Rank filtered values

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)

Lewis-H Post Partisan
Post Partisan

Re: Rank filtered values

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.

mbidelski
Frequent Visitor

Re: Rank filtered values

@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:

Adnotacja 2020-04-10 202742.png

 

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

Adnotacja 2020-04-13 154401.png

Vera_33 Resolver I
Resolver I

Re: Rank filtered values

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)

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors