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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
reiff
New Member

Return the count of a max value of a category with multiple filters

In a measure I want to return the max value product from a given time period and geography, and then count those across the entire table. Below is an example table, but each region has a max result across each year in the table. Thank you in advance, having trouble isolating the filters to apply correctly to find the max product.

Prodcut NameNumberYearRegion
Product1382442021South-east
Product297892021South-east
Product360102021South-east
Product4337532021South-east
Product528092021South-east
Product669752021South-east
Product1171152003North-west
Product295652003North-west
Product3176612003North-west
Product4213842003North-west
Product559492003North-west
Product1151562016Central
Product295882016Central
Product393452016Central
Product4159002016Central
Product5255182016Central
Product694092016Central


For 2021 in the South-east it should return Product1 as the highest number (38244)
For 2003 in the North-west it should return Product4 as the highest number (21384)
For 2016 in Central it should return Product5 as the highest number (25518)
etc.

Result would be something like this:

YearProduct1Product2Product3Product4Product5Product6
2021100000
2016000010
2003000100



1 REPLY 1
amitchandak
Super User
Super User

@reiff , if name same as index 1 order by measure partition by year

 

Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

 

example

if ( max( 'Item'[Brand]) =

maxx( index(1,SUMMARIZE(ALLSELECTED(sales), 'Item'[Brand], "_net",[Net]),ORDERBY([_net],DESC)),[Brand]),1,0)
 
Use product and your measure in this

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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