cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gmunker
Regular Visitor

Rank by number of occurrences

I have come across a lot of Rankx explnanations for ranking based on totals and sales and all that but i cannot figure out how to create a dynamic rank based on number of occurances. I have a list that repeats the name of the property and assesst numbers within the company, other information in different columns is the difference.

 

example: 

Assest NameIssuesProperty Name
123issue 1abc
123issue 2abc
434issue 1def
545issue 3ghi
567issue 1def
787issue 3def

 

What i am looking to do is create a ranking based on the Issues and a rank for the Property Name something like this.

 

Property NameRank
def1
abc2
ghi3

 

IssuesRank
issue 11
issue 32
issue 23

 

Everytime I have worked with this i get a rank of 1. The current formulas i am using is this. 

 

 

 

 

 

countOfProperties = COUNT('Table1'[Property Name])

 

 

 

 

This is just for easier refrences to the count for the rank.

 

 

 

 

rankOfPropertyName = RANKX(ALL('Table1'[Property Name]),'Table1'[countOfProperties])

 

 

 

Measure for the rank. 

 

Once again all I get is a 1 for each property, not a rank based on the number of occurrences for all the properties. 

 

Thank you in advance for any and all help! 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Gmunker 
Here is a sample file with the solution https://www.dropbox.com/t/wTwrgMl3Li4XNP2L

Count = COUNTROWS ( Table1 )
Name Rank = 
RANKX (
    ALL ( Table1[Property Name] ),
    [Count]
)
Issue Rank = 
RANKX (
    ALL ( Table1[Issues] ),
    [Count]
)

1.png

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @Gmunker 
Here is a sample file with the solution https://www.dropbox.com/t/wTwrgMl3Li4XNP2L

Count = COUNTROWS ( Table1 )
Name Rank = 
RANKX (
    ALL ( Table1[Property Name] ),
    [Count]
)
Issue Rank = 
RANKX (
    ALL ( Table1[Issues] ),
    [Count]
)

1.png

amitchandak
Super User
Super User

@Gmunker , If you add any other non summarized column(group by column) in visual, other than property name - your rank will be inside that one . like rank of property name under each issue.

 

same way property rank will not work with issue

 

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

Maybe i'm not understanding this as well, but this is all examples of values again. I need it over the number of occurances in my data. i'm still just getting 1's for each property. 😞 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors