Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ppgandhi11
Helper V
Helper V

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.  How would I create such a rank?

 

If there are only 3 specialty, then I want to list max 30 ProvierNPI (10 for each) that have max Total Amount paid. Any help is appreciated. Thanks!

1 ACCEPTED SOLUTION

Hi @ppgandhi11,

 

Please try this measure:

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

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
AndersMadsen
Helper I
Helper I

Use Dax to create a query

You want to use one of the filter functions to filter out the specialties
Then you want the TOPN function to take 10 records

https://www.mssqltips.com/sqlservertutorial/3910/sql-server-dax-sorting-and-ranking-data/ and a sortng function to weed out  the ones you don't want.

I am trying something like this, but it runs out of memory and cannot give the result.

 

TopPaidRankBasedOnSpecialty = CALCULATE((RANKX(ALLSELECTED(Query1[Provider Name]), [Total Payments])), GROUPBY(Query1, Query1[Specialty]))

 

(In the visual filter, TopPaidRankBasedOnSpecialty is set to: less than or equal to 10.)

I would make a custom measure to limit it to 10

 

Top10RankBasedOnSpecialty = TOPN(...)
https://msdn.microsoft.com/en-us/query-bi/dax/topn-function-dax?f=255&MSPPError=-2147217396

I am not getting a handle on this. I have mocked up some data. Can you please tell me how do i derive the Rank column or measure in this case? I am very new, so trying to get a grip on it. Capture.PNG

 

The Rank is based on TotalPaid Desc within each Specialty.

 

TotalPaid is a calculated measure as: TotalPaid = sum(BaseQuery[payor1pmt])

 

Thanks so much.  Eventually, I want to display all the data where Rank is less than 10. So basically it will display top 10 paid Providers for each category (specialty) - that part I know how to do - using visual filter on Rank with condition: less than or equal to 10. The problem is in deriving the Rank column above.

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.


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

Hi @ppgandhi11,

 

Please try this measure:

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

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.