Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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)
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.
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-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
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.
Hi @mbidelski it is because ALL removes all the filters, you can use ALLSELECTED
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
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)
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |