cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
paulpan1231 Frequent Visitor
Frequent Visitor

Getting the Max value of each group.

Capture - Power BI Question 1.JPG

 

 

Hello all! I am having a problem on showing the max value of each group. In this example, I am trying to get one row for each state group. I tried "Top N" filter but it's not working correctly in Power BI. Below is the ideal table I want to show in Power BI

Capture - Power BI Question 2.JPG

This is just a random data I created in Excel and my real file is over millions of rows so there is no way to do filter on one by one. Please let me know if anyone have a idea. Thank you so much!

1 ACCEPTED SOLUTION

Accepted Solutions
rajendran Super Contributor
Super Contributor

Re: Getting the Max value of each group.

Hi

 

if i understand your qn correctly, you want to get the max for country, state level. Made minor changes here.

 

 

Max in group = IF(CALCULATE(MAX(Table1[Score]),ALLEXCEPT(Table1,Table1[Country],Table1[State]))=MAX(Table1[Score])
          ,1,0)

 

Please note that i have changed the column name and sample data now. So the formula also changed accordingly.

Max in group 2.PNG

 

Thanks
Raj

View solution in original post

5 REPLIES 5
rajendran Super Contributor
Super Contributor

Re: Getting the Max value of each group.

Hi @paulpan1231

 

Create a Measure with this DAX

Max in group = IF(CALCULATE(MAX(Table1[Score]),ALLEXCEPT(Table1,Table1[Country]))=MAX(Table1[Score])
,1,0 )

And then apply a visual filter on this measure =1. Here is the output.

 

 

Max in Group.PNG

 

Thanks

Raj

paulpan1231 Frequent Visitor
Frequent Visitor

Re: Getting the Max value of each group.

Thank you so much Raj!

Your DAX measure works perfect in my example. However, my real project contains two filter and is that anywany I can do max in two filter? Imagine if there is a country column before state. For example

 

USA    NYC            Jack   10

USA    NYC            Ben    9

USA    LA               Sam   30

USA    LA               Tom   3

China  Beijing        Jon    5

India   New Delhi  Frank 6

 

Is there anyway I can get the max value for each city in USA?

I tried to use your function but it didn't show the "1" value when I have a slicer of Country.

 

Thank you so much for replying.

rajendran Super Contributor
Super Contributor

Re: Getting the Max value of each group.

Hi

 

if i understand your qn correctly, you want to get the max for country, state level. Made minor changes here.

 

 

Max in group = IF(CALCULATE(MAX(Table1[Score]),ALLEXCEPT(Table1,Table1[Country],Table1[State]))=MAX(Table1[Score])
          ,1,0)

 

Please note that i have changed the column name and sample data now. So the formula also changed accordingly.

Max in group 2.PNG

 

Thanks
Raj

View solution in original post

paulpan1231 Frequent Visitor
Frequent Visitor

Re: Getting the Max value of each group.

Thank you so much, Raj! That works perfect!!!

M_OVAISJANZEB Regular Visitor
Regular Visitor

Re: Getting the Max value of each group.

Hi @rajendran 

try ur code 

Max in group = IF(CALCULATE(MAXX('Revenue Traffic and Trends',[MTD RPT]),ALLEXCEPT(Location_Details,Location_Details[District_ID]))=MAX([MTD RPT])
,1,0)
 
make little change in it as Maxx please tell me why I am getting this error The MAX function only accepts a column reference as the argument number 1.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors