Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rohithkothaneth
Resolver I
Resolver I

RANK based on a account name column and a count of numbers for the respective accounts column

Hello Power Bi team,

I'm trying to list only top 5 records in my visualization, Below are the list of columns/measure used in my scenarios:

 

Tried with RANK, based on an Account Name column (in Text format) with another measure based on the count of Number (column name = Number) which is also used for ordering.  Issue is that Im getting ties and need help in restricting the result set to display only top 5 records.

 

Scenario 1:

Account Name -  in Text format

Ticket Number -  in Text format

Quarter year  - Need to use filter to show only those records for last quarter (have Column value as "2022_Q4"

 

Desired result:

Accounts Year_quarterRANK
Account 131
Account 222
Account 323
Account 424
Account 525
Account 626
Account 727
Account 818
Account 919
Account 10110
Account 11211

 

Scenario 2:

Account Name -  in Text format

Ticket Number -  in Text format

Quarter year  - Records for last 5 quarter

 

Desired output:

 

Accounts 2021_Q12021_Q22021_Q32021_Q42022_Q1TotalRANK
Account 11122019122651
Account 72711012322
Account 8680108323
Account 9790115324
Account 3221519295
Account 6168310286
Account 2281015267
Account 10913112268
Account 153915239
Account 5236372110
Account 4332281811

 

Thanks,
Rohith

1 ACCEPTED SOLUTION
rohithkothaneth
Resolver I
Resolver I

I was able to find a workaround to my issue.

Below are the steps taken to get the results, there could be other better solution. This is what I have per the power query method

 

1. Created a unique column by concatenating Accounts and Number column, this is what it was missing from the beginning, It is a TEXT column

2. Created a Ranked column based on the new concatenated column
3. Created a rank for my data requirement

 

Created a new column -

Concat Account Name = CONCATENATE('Table_Name'[Accounts ],'Table_Name'[Number])
 
Created another column -
 Named Rank = RANKX( ALL('Table_Name'), 'Table_Name'[Concat Account Name])
 
Created a new measure top get the ranking -
_Ranking =
RANKX(
ALLSELECTED('Table_Name'[Accounts]),
CALCULATE(COUNT('Table_Name'[Number]))+ INT(CALCULATE(MAX('Table_Name'[Named Rank])))/100000,
,DESC,Dense
)

 

Accounts NumberAccountsNumber_NamedRank2021_Q1RANK
Account 11A12Account 11A125121
Account 7A13Account 7A13422
Account 8A14Account 8A14363
Account 9A15Account 9A15274
Account 3A16Account 3A16125

 

Thanks,
Rohith

View solution in original post

1 REPLY 1
rohithkothaneth
Resolver I
Resolver I

I was able to find a workaround to my issue.

Below are the steps taken to get the results, there could be other better solution. This is what I have per the power query method

 

1. Created a unique column by concatenating Accounts and Number column, this is what it was missing from the beginning, It is a TEXT column

2. Created a Ranked column based on the new concatenated column
3. Created a rank for my data requirement

 

Created a new column -

Concat Account Name = CONCATENATE('Table_Name'[Accounts ],'Table_Name'[Number])
 
Created another column -
 Named Rank = RANKX( ALL('Table_Name'), 'Table_Name'[Concat Account Name])
 
Created a new measure top get the ranking -
_Ranking =
RANKX(
ALLSELECTED('Table_Name'[Accounts]),
CALCULATE(COUNT('Table_Name'[Number]))+ INT(CALCULATE(MAX('Table_Name'[Named Rank])))/100000,
,DESC,Dense
)

 

Accounts NumberAccountsNumber_NamedRank2021_Q1RANK
Account 11A12Account 11A125121
Account 7A13Account 7A13422
Account 8A14Account 8A14363
Account 9A15Account 9A15274
Account 3A16Account 3A16125

 

Thanks,
Rohith

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.