Getting the Max value of each group.

[Anonymous]

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

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!

[diro]

can we sum or count column "Max in Group"?

[Anonymous]

Hi @Anonymous

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.

Thanks

Raj

[Anonymous]

Hi @Anonymous 

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.

[Anonymous]

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.

[Anonymous · Accepted solution]

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.

Thanks
Raj

[aditichowbey]

Hi, 

I am trying to acheive the same thing and I am using this logic: 

It is not an amount, but a date. 

Latest entry = if(

    CALCULATE(

        max(X[UPDATE_DATE].[Date])

    ,ALLEXCEPT(

        X[ClientID]

    )

    ) = max(X[UPDATE_DATE].[Date])

    ,1,0

    )

This logic only returns 1. I also tried using LASTDATE but that gave me the same problem. Thank you in advance for your help!

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.

[lopa123]

Hi, does somebody know why in powerpivot Excel this formula return different result that in power bi? thanks!

[Anonymous]

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