Calculated Column: find item based on max of another column within group

[Anonymous]

Hello,

Having difficulties writing a dax formula for a calculated column. I have a list of work orders in a table. I want to create a column called "Primary Output" that will tell me the item number within the current work order that has the highest output weight. Here is a sample including my desired result:

Order No.	Item No.	KG	Primary Output
A	12	45	12
A	13	26	12
A	14	27	12
B	20	29	21
B	21	42	21
B	22	16	21
C	31	85	33
C	32	64	33
C	33	855	33

Thank you in advance for your assistance!

Michael

[harshnathani · Accepted solution]

Hi @Anonymous ,

Try this Calculated Column

Column =
VAR a =
    CALCULATE (
        MAX ( 'Table'[KG] ),
        FILTER (
            'Table',
            'Table'[Order No.]
                = EARLIER ( 'Table'[Order No.] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Item No.] ),
        FILTER (
            'Table',
            'Table'[KG] = a
        )
    )

Regards,
Harsh Nathani

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