Find Largest Value, then pull value in another column based off each portfolio code

[gmasta1129]

Hello,

I am looking to create a new column where the formula pulls the value in column C, based off the highest value in column B. Also, this needs to be done by each portfolio code (column A).  

Example #1, portfolio code "30022", the highest value in column B is 5. The new column I am trying to create should pull in the value from column C which is 50.  But this should pull in for all rows that contain portfolio code 30022.  

Example #2, portfolio code "30015", the highest value in column B is 4.  The new column I am trying to create should pull in the value from column C which is 25.  But this should pull in for all rows that contain portfolio code 30015.

[Bibiano_Geraldo · Accepted solution]

HI @gmasta1129 ,

You can achieve your goal by creating a new calculated colum using the following DAX:

New Column = 
VAR MaxValue = 
    CALCULATE(
        MAX('Table'[Value]),
        ALLEXCEPT('Table', 'Table'[Portfolio Code Value])
    )
RETURN
    CALCULATE(
        MAX('Table'[Value #2]),
        'Table'[Value] = MaxValue,
        ALLEXCEPT('Table', 'Table'[Portfolio Code Value])
    )

Your table now should look like this:

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

Column = LOOKUPVALUE(Data[Value #2],Data[Value],CALCULATE(max(Data[Value]),FILTER(Data,Data[Portfolio Code]=EARLIER(Data[Portfolio Code]))),Data[Portfolio Code],Data[Portfolio Code])

Hope this helps.

[Bibiano_Geraldo · Accepted solution]

HI @gmasta1129 ,

You can achieve your goal by creating a new calculated colum using the following DAX:

New Column = 
VAR MaxValue = 
    CALCULATE(
        MAX('Table'[Value]),
        ALLEXCEPT('Table', 'Table'[Portfolio Code Value])
    )
RETURN
    CALCULATE(
        MAX('Table'[Value #2]),
        'Table'[Value] = MaxValue,
        ALLEXCEPT('Table', 'Table'[Portfolio Code Value])
    )

Your table now should look like this: