Forum Discussion

kabra_ashish's avatar
kabra_ashish
Icon for Helper III rankHelper III
6 years ago
Solved

Finding MAX in each category

Need some quick help.

 

I have a table in my data model as below:

 

Record       Name           Dates                Transactions (#)

1                   A              Jan-01-2020                115

2                   A              Feb-21-2020               200

3                   A              Mar-12-2020               80

4                   B              Feb-11-2020                75

5                   B              Mar-29-2020              120

 

I want to create a 4th column which tells me if that particular row has the highest transaction for that particular customer in "Yes" or "No"? So basically I am looking for a "Yes" in  record 2 for Customer A and 5 for Customer B and the rest as "No". Is this doable?

 

Thanks for all the help!

  • AllisonKennedy's avatar
    AllisonKennedy
    6 years ago
    You could try this as a calc column:

    IsMax - IF( 'Table1'[Transaction] a MAXX(FILTER(ALL('Table1'), 'Table1'[Name] ? EARLIER('Table1'[Name])), 'Table1'[Transaction]), "Yes", "No")

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi kabra_ashish ,

     

    Maximum Transaction =

     

    var max = MAXX(FILTER('Table', 'Table'[Name] = EARLIER('Table'[Name]))

     

    RETURN

    IF( 'Table'[Transaction] =  max ,  "Max Value", BLANK())

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

    • kabra_ashish's avatar
      kabra_ashish
      Icon for Helper III rankHelper III
      6 years ago

      Thanks Anonymous 

       

      However it throws and error in the variable created. Its says "Too few arguments were passed to the MAXX function. The minimum argument count for the function is 2."

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI kabra_ashish ,

         

        I missed adding Table[Transactions]

         

         

        Maximum Transaction =
VAR _max =
    MAXX (
        FILTER (
            'Table',
            'Table'[Name]
                = EARLIER ( 'Table'[Name] )
        ),
        Table[Transaction]
    )
RETURN
    IF (
        'Table'[Transaction] = _max,
        "Max Value",
        BLANK ()
    )

         

        Also, the solution from AllisonKennedy  will work.  

         

         

        Regards,

        Harsh Nathani

         

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago
    You could try this as a calc column:

    IsMax - IF( 'Table1'[Transaction] a MAXX(FILTER(ALL('Table1'), 'Table1'[Name] ? EARLIER('Table1'[Name])), 'Table1'[Transaction]), "Yes", "No")
    • kabra_ashish's avatar
      kabra_ashish
      Icon for Helper III rankHelper III
      6 years ago

      All the solutions provided by  AllisonKennedy mahoneypat Anonymous have actually worked.

       

      Can I add one more similar question to it? This will remove the unnecessary tables and improve the data model.

       

      Lets say I have just 1 field:

       

      Field:

      A01/01/2020

      A01/01/2020

      A01/01/2020

      A03/12/2020

      B02/11/2020

      B03/29/2020

       

      Is it possible to add a new calculated column which give the counts:

      Example: records 1,2 and 3 will give 3 each because they belong to the same category (A01/01/2020) and then 4,5,6 gives 1 each.  

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI kabra_ashish ,

         

        Create a measure = CALCULATE(COUNTA(Table[id]) , FILTER(ALL(Table) , Table[id] = MAX(Table[id]))

         

        Regards,
        Harsh Nathani
        Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Please try this expression, replacing Table with your actual table name.

     

    Is Max =
    VAR thismax =
    SUM ( Table[Transactions (#)] )
    VAR maxthiscustomer =
    CALCULATE ( SUM ( Table[Transactions (#)] ), ALLEXCEPT ( Table, Table[Name] ) )
    RETURN
    IF ( thismax = maxthiscustomer, "Yes", "No" )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    kabra_ashish , as new column

    if([Transactions (#)] = maxx(filter(table,[Name] =earlier([Name])),[Transactions (#)]),"Yes", "No")