Forum Discussion

Rymatt830's avatar
Rymatt830
Advocate II
10 years ago
Solved

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:

 

I 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!

 

Need Help
  • Vvelarde's avatar
    Vvelarde
    10 years ago

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

7 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    10 years ago

    In table tblFinancialAudit: Create a calculated column:

     

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

    • Rymatt830's avatar
      Rymatt830
      Advocate II
      10 years ago

      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)

       

       

       

      • Sean's avatar
        Sean
        Community Champion
        10 years ago

        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! :smileyhappy: