Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

Ranking and filtering top 10 with multiple rows in Power BI Matrix

Dear BI community,

 

In the below Power BI Matrix (filters applied: fiscal year is 2020-21 and supplier is supplierX), I have 2 rows being firstly Area of Law (12 of these), and Matter Name(hundreds of these). In the values I have client name, matter start date, and total invoiced amount. What I am trying to do is filter out to the top ten matters by total amount invoiced under each area of law (highlighted in sky blue). I thought I could achieve this by creating a rankx measure but it is not working as I expected. How can I proceed so that each area of law section has only the top ten matters invoiced within each area of law? I also do not have access to the advanced filter where I can filter by N on the values.

 

Please let me know, thank you for your help.

 

AndrewPanchal_0-1641963668903.png

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Syndicated - Outbound

@Anonymous , Assumed Total Amount Invoiced is a measure. Try a measure like and filter for <=10


Rankx(filter(summarize(allselected(Table), Table[Area of Law], Table[Client Name]), [Area of Law] = max([Area of Law]) ), [Total Amount Invoiced],,desc, dense)

 

or refer

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

Syndicated - Outbound

@Anonymous , Assumed Total Amount Invoiced is a measure. Try a measure like and filter for <=10


Rankx(filter(summarize(allselected(Table), Table[Area of Law], Table[Client Name]), [Area of Law] = max([Area of Law]) ), [Total Amount Invoiced],,desc, dense)

 

or refer

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
avatar user
Anonymous
Not applicable

Syndicated - Outbound

Thank you very much Amit. I referred to your link and was able to make the code to solve this issue perfectly. Sincerely appreciated.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)