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

AndersMadsen's avatar
AndersMadsen
Icon for Helper I rankHelper I
8 years ago

In your dax query use one of the filter functions. In this case Filter() Should suffice.

 

FILTER(NameOfYourTable;[Rank] <= 10)

 

if you already have a filter in your dax query plug this expression in.

 

https://msdn.microsoft.com/en-us/query-bi/dax/dax-operator-reference just in case you need reference on the logical and/or operators

 

https://community.powerbi.com/t5/Desktop/Count-rows-less-than-column-value/td-p/54246 Here's someone that almost needed what you need, it shows how to plug in and use multiple functions in a dax query.


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?