Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Require help with creating 2 levels of Ranks

hi, I am trying to create a rank at 2 level - Region and State per say based on a calculated measure (wages / sales). I have Sales and Wages data at day level and have dimension tables for location - can you please help on how I can achieve this. I have been trying to implement this using RANKX but not giving me the desired output.
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    Try the following measures:

     

    Rank by District =
IF (
    HASONEVALUE ( Branch[Branch] );
    RANKX ( ALLSELECTED ( Branch[Branch] ); ( [Ratio] );; ASC; SKIP )
)



Rank by Region =
IF (
    HASONEVALUE ( Branch[Branch] );
    RANKX ( ALL ( Branch ); ( CALCULATE ( [Ratio] ) );; ASC; SKIP )
)

     

    The branch table is you location table. I'm assuming your Ratio is a measure, altough on the sample table you provide the calculation between wages and sales for district 2 is not matching.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Sorry for some reason could not put in the sample data and desired output in the original post - looking to calculate Column G & H in the below screenshot

     

     

    Regards,

    Sumanth

     

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago

      Hi Anonymous ,

       

      Try the following measures:

       

      Rank by District =
IF (
    HASONEVALUE ( Branch[Branch] );
    RANKX ( ALLSELECTED ( Branch[Branch] ); ( [Ratio] );; ASC; SKIP )
)



Rank by Region =
IF (
    HASONEVALUE ( Branch[Branch] );
    RANKX ( ALL ( Branch ); ( CALCULATE ( [Ratio] ) );; ASC; SKIP )
)

       

      The branch table is you location table. I'm assuming your Ratio is a measure, altough on the sample table you provide the calculation between wages and sales for district 2 is not matching.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        hi MFelix - Thank you for your quick response; I shall give this a shot and get back to you.. 

         

        Also yes, Ratio is a measure - the mock data got a little messed up. Thanks!