Forum Discussion

ppgandhi11's avatar
ppgandhi11
Icon for Helper V rankHelper V
8 years ago
Solved

group by help in creating rank

Hi,   I have below fields in my query.   ProviderNPI - field Total Amount Paid - measure Specialty - field   I want to create a rank of top 10 highest paid providerNPI within each Specialty. ...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi ppgandhi11,

     

    Please try this measure:

    Rank = RANKX(ALLEXCEPT(Query1,Query1[Specialty]),[TotalPaid],,DESC)

     

    Best regards,

    Yuliana Gu

ppgandhi11's avatar
ppgandhi11
Icon for Helper V rankHelper V
8 years ago

AndersMadsen OK I will try it out today and let you know where I end up with. Thanks!

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi ppgandhi11,

 

Please try this measure:

Rank = RANKX(ALLEXCEPT(Query1,Query1[Specialty]),[TotalPaid],,DESC)

 

Best regards,

Yuliana Gu

  • LucMarv's avatar
    LucMarv
    Icon for Helper I rankHelper I
    4 years ago

    Would you be able to explain in words what the 'AllExcept' does? To me, it is not at all inuitive why the 'AllExcept' needs to be used here.

    Thanks a lot!

  • LucMarv's avatar
    LucMarv
    Icon for Helper I rankHelper I
    4 years ago

    Also, is there a way to make this work on >1 columns for grouping... So in this example, we take the top rank based on speciality, but what if we want to take the top 10 based on speciality and another variable?