Finding MAX in each category

[kabra_ashish]

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!

[amitchandak]

@kabra_ashish , as new column

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

[mahoneypat]

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

[harshnathani]

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]

Thanks @harshnathani 

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."

[harshnathani]

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 · Accepted solution]

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]

All the solutions provided by  @AllisonKennedy @mahoneypat @harshnathani 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.  

[kabra_ashish]

Thank you @harshnathani. This is really amazing!

Sorry too many questions but this is my last one (which is basically a combination of my first and previous question) - In the below example A01/01/2020 has the highest count (appearing thrice) so I need a "Yes" in front of them. Simlarly B has the highest on 24/02/2020 (appearing twice). If you can help me here as well that would be really helpful so that I can close my series of questions.

Name:                        Column (needed)

A01/01/2020                  Yes

A01/01/2020                  Yes

A01/01/2020                  Yes

A15/03/2020                  No

A20/04/2020                  No

B12/01/2020                  No

B24/02/2020                  Yes

B24/02/2020                  Yes

[v-yingjl]

Hi @kabra_ashish ,

You can create this calculated column:

Result = 
VAR tab =
    ADDCOLUMNS (
        'Table',
        "Count", CALCULATE ( COUNT ( 'Table'[Name] ), FILTER(ALL('Table'),'Table'[Name] = EARLIER ( 'Table'[Name] ) )),
        "_Name", LEFT ( 'Table'[Name], 1 )
    )
VAR newtab =
    ADDCOLUMNS (
        tab,
        "Re",
        VAR _max =
            MAXX ( FILTER ( tab, [_Name] = EARLIER ( [_Name] ) ), [Count] )
        RETURN
            IF ( [Count] >= _max, "Yes", "No" )
    )
RETURN
    MAXX ( FILTER ( newtab, [Name] = EARLIER('Table'[Name]) ), [Re] )

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[harshnathani]

Hi @kabra_ashish ,

You will need to separate A and B in a separate Column.

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

[harshnathani]

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)

[harshnathani]

HI @kabra_ashish ,

You can also create a colun

Column = COUNTX(FILTER('Table', 'Table'[Id] = EARLIER('Table'[Id])),'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)