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

RANKX Help

I can't seem to get the RANKX function to work. I have searched what I think is every other post and tried all the solutions but nothing has worked. I am trying to return the annual rank by peer group for each organization based on audit data. There are approximately 250 organizations and audit data for each organization for each year from 2010-2014. The organizations have a unique ID and all belong to a peer group (PeerGroupAC). Here's the data:

 

e.PNGI have tried several variations of the formula:

  • Rank = RANKX(ALL(tblOrganization[ID]), tblFinancialAudit[CurrentAssets])
  • Rank = RANKX(ALLSELECTED(tblOrganization[ID]), tblFinancialAudit[CurrentAssets])
  • Rank = RANKX(ALL(tblOrganization[ID]), SUM(tblFinancialAudit[CurrentAssets]))
  • etc.

I also tried to create a measure, SumCurrentAssets = SUM(CurrentAssets), and reference it in the RANKX formulas.

  • SumCurrentAssets = SUM(CurrentAssets)
  • Rank = RANKX(ALL(tblOrganization[ID]), [SumCurrentAssets])

At this point, nothing has worked and I would love some expert advice, please! Thanks!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Vvelarde Super Contributor
Super Contributor

Re: RANKX Help

Ranking =
RANKX (
    FILTER (
        ALL ( tblFinancialAudit );
        tblFinancialAudit[year] = EARLIER ( tblFinancialAudit[Year] )
            && tblFinancialAudit[PeerGroup] = EARLIER ( tblFinancialAudit[PeerGroup] )
    );
    tblFinancialAudit[CurrentAssets];
    ;
    ASC;
    DENSE
)




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




7 REPLIES 7
Vvelarde Super Contributor
Super Contributor

Re: RANKX Help

In table tblFinancialAudit: Create a calculated column:

 

Rank = RANKX(FILTER(ALL(tblFinancialAudit);tblFinancialAudit[year]=EARLIER(tblFinancialAudit[Year]));tblFinancialAudit[CurrentAssets];;ASC;Dense)




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Rymatt830 Regular Visitor
Regular Visitor

Re: RANKX Help

Thank you, Vvelarde. I implemented your suggestion (except I replaced ";" with ",") but it's not recognizing the PeerGroupAC field. There are only 21 organizations in that Peer Group, but the ranks appear to be based on all of the organizations, not just the one's in the selected group (see image)

 

Update.PNG

 

 

Sean Super Contributor
Super Contributor

Re: RANKX Help

@Rymatt830 Check which [ID] column you are using to build your Table Visualization?

 

If your Ranking Measure look like this...

 

you should use tblOrganization[ID] to build your Table NOT tblFinancialAudit[ID]

 

Rank =
RANKX (
    ALL ( tblOrganization[ID] ),
    CALCULATE ( SUM ( tblFinancialAudit[CurrentAssets] ) )
)

 

I hope this resolves it! Smiley Happy

Rymatt830 Regular Visitor
Regular Visitor

Re: RANKX Help

Thanks for the reply. I tried that solution initially and it does not work.

Vvelarde Super Contributor
Super Contributor

Re: RANKX Help

Ranking =
RANKX (
    FILTER (
        ALL ( tblFinancialAudit );
        tblFinancialAudit[year] = EARLIER ( tblFinancialAudit[Year] )
            && tblFinancialAudit[PeerGroup] = EARLIER ( tblFinancialAudit[PeerGroup] )
    );
    tblFinancialAudit[CurrentAssets];
    ;
    ASC;
    DENSE
)




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Rymatt830 Regular Visitor
Regular Visitor

Re: RANKX Help

Thank you again, Vvelarde! Your solution works great!

BiMK Regular Visitor
Regular Visitor

Re: RANKX Help

Hello @Vvelarde,

 

I'm using the same formula for my Rank measure and I don't know why but it doesn't seem to work properly..

The rank starts from 2.. look

 

 http://prntscr.com/e5m25q

 

 (I don't know why I can not send the screenshot as a picutre in the post...)

 

Thanks

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 39 members 1,044 guests