Forum Discussion
Helper Vgroup 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!
Hi ppgandhi11,
Please try this measure:
Rank = RANKX(ALLEXCEPT(Query1,Query1[Specialty]),[TotalPaid],,DESC)
Best regards,
Yuliana Gu
9 Replies
AndersMadsenHelper 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 recordshttps://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.
ppgandhi11I 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.)
- AndersMadsen
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