Forum Discussion
Rank Dynamically with several columns in a table
Hello,
I need to rank dynamically the dataset below by Area Leased column.
| Tenant Name | Quarter | Year | Address | State | Suburb | Area Leased |
| Jon K | Q1 | 2021 | 250 Victoria Street | NSW | Sydney | 70,178 |
| Michel F | Q1 | 2021 | 285 Palmers Road | VIC | Melbourne | 70,000 |
| Huan Jyan | Q1 | 2021 | 917 Boundary Road | VIC | Melbourne | 70,000 |
| Justin T | Q1 | 2021 | Lot 4, Skyline Crescent | NSW | Sydney | 36,171 |
| Chin Y | Q1 | 2021 | 2 Maker Place | VIC | Melbourne | 30,000 |
| Wayne T | Q1 | 2021 | 500 Dohertys Road | VIC | Melbourne | 25,000 |
| Bryan Y | Q1 | 2021 | 11-13 Percy Street | NSW | Sydney | 20,480 |
| Ahram J | Q1 | 2021 | 11-167 Palm Springs Road | VIC | Melbourne | 19,960 |
The outcome I need:
For example, if the user selects "3" in the slicer, the table above must show only the first 3 tenants' names with the higher Leased Area.
Thanks
Hi, Anonymous
Thank you for your feedback.
Because the condition has been changed, I amended the measures.
Showing the amount by ranking of amount + alphabetical order + not showing blank name.
I did not consider the ranking by each area name. If you want to, you can simply add in the condition for area or city name.
Also, if you want to have a slicer, you can simply replace the ALL function to ALLSELECT in order to dynamically be changed by the date-slicer. I already changed to allselect.
Please check the link down below, and all measures are in the sample pbix file.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
https://www.dropbox.com/s/36yyza7yooeqbdn/fabnishiv2.pbix?dl=0
5 Replies
Anonymous , You can create a measure rank
Rankx(allselected(Table[Tenant Name]), calculate(Sum(Table[Area Leased])),,desc,dense)
or
Rankx(allselected(Table), calculate(Sum(Table[Area Leased])),,desc,dense)
You can filter for 3 in visual level
of you can use TOP N with what if
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-finaleHi, Anonymous
Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.
Show Info by Top Rank Select =VAR toprankselect =SELECTEDVALUE ( Ranking[Ranking] )RETURNSUMX (KEEPFILTERS (TOPN (toprankselect,ALL ( 'Table' ),CALCULATE ( SUM ( 'Table'[Area Leased] ) ), DESC)),CALCULATE ( SUM ( 'Table'[Area Leased] ) ))Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Hi, Jihwan_Kim thanks for getting back to me. The solution almost worked. In my real data set I actually have the same Numbers and null please see below the first and last row that I added
Essentially the tenant FK and Ahram J have the same area leased of 19,960 however the rank cannot be the same for them, the Ahram must be first because of the alphabetical order.
We also need to ignore the tenant name with empty name and value on the area leased.
Tenant Name Quarter Year Address State Suburb Area Leased
Q1 2021 Lot 4, Skyline Crescent NSW 100,000
Jon K Q1 2021 250 Victoria Street NSW Sydney 70,178
Michel F Q1 2021 285 Palmers Road VIC Melbourne 70,000
Huan Jyan Q1 2021 917 Boundary Road VIC Melbourne 70,000
Justin T Q1 2021 Lot 4, Skyline Crescent NSW Sydney 36,171
Chin Y Q1 2021 2 Maker Place VIC Melbourne 30,000
Wayne T Q1 2021 500 Dohertys Road VIC Melbourne 25,000
Bryan Y Q1 2021 11-13 Percy Street NSW Sydney 20,480
Ahram J Q1 2021 11-167 Palm Springs Road VIC Melbourne 19,960
F K Q1 2021 500 Dohertys Road VIC Melbourne 19,960I also have the date linked to my dim_date which I need to reset the rank when changing the dates, is that possible?
Hi, Anonymous
Thank you for your feedback.
Because the condition has been changed, I amended the measures.
Showing the amount by ranking of amount + alphabetical order + not showing blank name.
I did not consider the ranking by each area name. If you want to, you can simply add in the condition for area or city name.
Also, if you want to have a slicer, you can simply replace the ALL function to ALLSELECT in order to dynamically be changed by the date-slicer. I already changed to allselect.
Please check the link down below, and all measures are in the sample pbix file.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
https://www.dropbox.com/s/36yyza7yooeqbdn/fabnishiv2.pbix?dl=0
