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.
I've had a look round and cannot find a potential solution to my issue.
I'm looking to filter a table visual to just 5 rows based on ranking of sales and a Agent Slicer. So my table has 3 columns Agent, Sales Date and Sales and a Rank measure based on sum sales. One table visual is easy enough to do is Top 3 but I need another table to show by rank just 5 other agents based on a slicer Agent seletion.
So if the Agent that is selected is ranked 50th, in the table I need to see the two agents ranked above and below so ranks 48th, 49th, 50th, 51st and 52nd. So basically you can see a selected groups performance against the top 3 table visual.
Hi @ChrisPBIUser ,
Hello, I cannot open the link you shared. You need to upload it to OneDrive and then share link. This method is preferable. Looking forward to your reply as soon as possible, I will solve your problem as soon as possible.😀
Best Regards,
Henry
@v-henryk-mstf thanks for assisting, I've just tried the link above which is one drive and the Excel file opens for me but that's probably because it's my One Drive - https://onedrive.live.com/edit.aspx?resid=E9A224AF3C65C846!10013&ithint=file%2cxlsx&authkey=!ACDftt4...
The file is simple, three columns. Order Date, Agent and Sales and about 9000k rows. So different daily dates from 2017 through to 2020, about 300 names for agents and a sales value amount. This is just random data I pulled from the net as I'll be able to apply any answer to the actual data I have
Hi @ChrisPBIUser ,
The link you shared is password protected, you need to follow the sharing steps below:
Best Regards,
Henry
@v-henryk-mstf , knew it would be me, try this link https://1drv.ms/x/s!AkbIZTyvJKLpzh0abswIDDLsYHkf?e=uyvDuO
@amitchandak morning, I've been playing around with this but I still get errors on the original answer or I just get the single selected value. Just wondering if you have had chance to look into this
@amitchandak thanks again for your quick response, I can see what it's trying to do but I get an error which I can't resolve image below plus image of the two tables I now have.
@ChrisPBIUser , In case only need rank based on Agent, then you should not have date in the tbale
Rankx(allselected(Table[Agent]), [Sales]) //Sales is a measure
Rankx(allselected(Table[Agent], Table[Date]), [Sales]) //Sales is a measure, with agent and date
You can now use visual level filter
You can also use TOPN
https://www.youtube.com/watch?v=QIVEFp-QiOk
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
@amitchandak thanks for the quick response.
The measure I have in place is
@ChrisPBIUser , for that you need an independent Agent table and then try a measure like
measure =
var _rank= maxx(filter(values(Orders[Agent]),[Agent] in selectedValue(Agent[Agent])),[Rank])
return
calculate([Rank], filter(values(Orders[Agent]), [Rank] >= _rank -2 && [Rank] >= _rank +2))
Refer independent table -https://www.youtube.com/watch?v=lOEW-YUrAbE
@ChrisPBIUser , you can upload to dropbox or onedrive and share a link
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 |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |