RANK by category?

[goatmonslc]

Hello all!

Looking for some guidance on how to resolve the following scenario.

I have two tables:

FACTS

RecordID (number)

BusinessUnit (text)

ReportNum (text)

CODES TABLE

Code (text)

CodeType (text)

ReportNum (text)

I have a relationship between these two tables using ReportNum (one->many). I'm trying to create a matrix table that will show the top 5 codes for each BusinessUnit using the count of the ReportNum. I've read lots of posts, etc. about RANKX but a lot of the examples use calculation of "total sales" or other numerical values. I've tried replacing some of the code with COUNT instead, but I am still not getting the results I'm looking for. It seems to be calculating the same total count for each business unit and code. Either that, or it's calculating the ranking based on just the code, and not the BU+Code. Here is what I am looking for, as far as output in a visualization (matrix table form):

Business Unit/Code

BU1

Insufficient Information, 120

Foreign Matter, 99

Swelling, 15

Seroma, 6

Pain, 2

BU2

Discomfort, 344

Blister, 42

Headache, 41

Fatigue, 12

Pain, 9

Any help would be sooo appreciated!

Dee

[goatmonslc · Accepted solution]

Sorry for so many replies! I figured out that this is working as long as I have all of the data in one table. I wasn't sure how to rank the count of codes, where the codes were in the "many" table of the one-many relationship between my facts table and my code table. What I ended up doing was adding a new calculated column into my CODES table to pull the name of the BusinessUnit in using the RELATED calculation. Now if I perform my ranking all in one table (the CODES table) it's working fine. I'm still curious how this would be able to work, however, using both tables!

[Anonymous]

Hi, @goatmonslc 

It looks like you have found a solution. Could you please mark this helpful post as “Answered”?

This will help others in the community to easily find a solution if they are experiencing the same problem as you.

Thank you for your cooperation!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PabloVallejo12]

Did you try to create a simple measure with the count, and then use it to filter the visualize with a rank?

[goatmonslc]

Yes, I have a measure called "CodeCount":

CodeCount = COUNT('Codes'[ReportNum])

My rank:

CodeRank = RANKX(ALL('Codes'[Code]),Measures_Table[CodeCount])

When I use the CodeRank to filter the visual (CodeRank<6) it doesn't actually filter anything under the BusinessUnit. I get all of the counts for all of the codes under each BusinessUnit. I have tried different rank measures, but can't seem to get it right.

[goatmonslc]

OK the count is working, my Rank shows up with ranking 1 for each BusinessUnit 'Code'.