How to calculate rank by count of occurances per category

[Ski900]

I have a table where I am trying to assign a rank based on the count of each category. For example, below on the left is the original column and on the right is the desired results. One issue that I recognize is that I will have duplicate ranks, i.e. Category B and D both have a rank of 2. I am not sure how to handle this, or if it's even possible to handle. I am unable to use dates (to my knowledge) because the data set comes in with the date in Month Year format (April 2018). Any help is much appreciated.

(Original)		(Desired Results)
Category		Category	Count of Category	Rank
A		A	4	1
A		B	3	2
A		C	1	3
B		D	3	2
B
B
C
D
A
D
D

[tlo11]

Hey there,

I have a similar issue that for some reason I cant figure out how to get it working with Dax.

So (part of) my data model looks like that:

• Table Applicants with three columns: Name of Applicant, Code of Job Offering, Count of Applications (could be more than one)

What I want to generate is a rank that ranks a job offering based on the total count of applications (i.e. the job.  This should be something like this?

Ranking = RANK.EQ(SUM('Applications'[CountOfApplications]),'Applications'[CodeOfJobOffering],DESC)

Any help is greatly appreciated!

[Ashish_Mathur]

Hi,

Drag Category to the Table visual and try these measures

Category Count = COUNTROWS(Data)

Rank = RANKX(ALL(Data[Category]),[Category Count])

Hope this helps.

[Greg_Deckler · Accepted solution]

If all you have is a table listing categories, then perhaps something like this:

Measure = 
VAR __currentCategory = MAX(CategoryRanks[Category])
VAR __tmpTable = ALL(CategoryRanks)
VAR __tmpTable1 = SUMMARIZE(__tmpTable,CategoryRanks[Category],"__Count",COUNT(CategoryRanks[Category]))
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__Rank",RANKX(__tmpTable1,[__Count]))
RETURN MAXX(FILTER(__tmpTable2,[Category]=__currentCategory),[__Rank])

[MeNotARobot]

99% done. Change add "Dense" to RANKX:

Measure = 
VAR __currentCategory = MAX(CategoryRanks[Category])
VAR __tmpTable = ALL(CategoryRanks)
VAR __tmpTable1 = SUMMARIZE(__tmpTable,CategoryRanks[Category],"__Count",COUNT(CategoryRanks[Category]))
VAR __tmpTable2 = ADDCOLUMNS(__tmpTable1,"__Rank",RANKX(__tmpTable1,[__Count]))
RETURN MAXX(FILTER(__tmpTable2,[Category]=__currentCategory),[__Rank],,,Dense)

[munkherdenez]

What does Dense do in RANKX?

[Ski900]

Thank you so much! I was so far off base. Is there a benefit to creating var's versus nesting them?

[Greg_Deckler]

Sometimes you definitely need to use VAR's for functionality but it's mostly readability. Just say no to Perl code...