Rank Dynamically with several columns in a table

[Anonymous]

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

[Jihwan_Kim]

Hi, @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] )

RETURN

SUMX (

KEEPFILTERS (

TOPN (

toprankselect,

ALL ( 'Table' ),

CALCULATE ( SUM ( 'Table'[Area Leased] ) ), DESC

)

),

CALCULATE ( SUM ( 'Table'[Area Leased] ) )

)

https://www.dropbox.com/s/36yyza7yooeqbdn/fabnishiv2.pbix?dl=0 

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/

[Anonymous]

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,960

I also have the date linked to my dim_date which I need to reset the rank when changing the dates, is that possible?

[Jihwan_Kim · Accepted solution]

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 

[Anonymous]

Hi @Jihwan_Kim 

Thank you! this is working as I expected.

[amitchandak]

@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://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

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