Max value of a column based on another column's value

[Anonymous]

Hello, I'm trying to sum the max values of a column (Opportunity Age in Days) based on another column (Opportunity Number). The challenge is when the Opportunity Number appears more than once. For example, 640201 appears twice, but the value of 519 should also be factored into the calculation once.  Tried a few different measures without any luck. Hoping someone will show me the light. Thanks in advance. 

Opportunity Number	Opportunity Age in Days
729972	27
669690	309
640201	519
640201	519
673227	141
668483	190
699579	16
662641	217
732597	71
677280	98
864073	26
661032	214

[Anonymous]

Hi @Anonymous ,

I checked FreemanZ's code and I think you just need _table part to sum the max age for each number.

Measure = 

VAR _table =

ADDCOLUMNS(

    VALUES(data[Opportunity Number]),

    "AgaMax",

    CALCULATE(MAX(data[Opportunity Age in Days]))

)

RETURN

SUMX(_table,[AgaMax])

 Result is as below.

Best Regards,
Rico Zhou

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

[Anonymous]

Thank you both. Should provide a bit more context...I'm using Excel's data model, not PBI. Was looking for some assistance with a Dax measure that would sum the 'Opportunity Age in Days' field, but only by unique Opportunity Number. This measure will be used in conjuction with the unique count [of Opportunity Number] to derive the avg sales cycle:  Avg Sales Cyle = Total Days / Deal Count. I'm struggling with Total Days measure so it excludes those values where the Opportunity Number is listed more than once. 

[FreemanZ]

hi @Anonymous 

try to create a measure like this: 

Measure =

VAR _table =

ADDCOLUMNS(

    VALUES(data[number]),

    "AgaMax",

    CALCULATE(MAX(data[Age]))

)

VAR _table1 =

TREATAS(

     _table,

     data[number],

     data[Age]

)

RETURN

SUMX(_table1, [AgaMax])