Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Hi @ppgandhi11,
Please try this measure:
Rank = RANKX(ALLEXCEPT(Query1,Query1[Specialty]),[TotalPaid],,DESC)
Best regards,
Yuliana Gu
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.
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)
Best regards,
Yuliana Gu
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!
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |