Forum Discussion
Rank filtered values
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?
9 Replies
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.
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
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-finaleAppreciate 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
