Forum Discussion

gmasta1129's avatar
gmasta1129
Icon for Resolver I rankResolver I
1 year ago
Solved

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

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.

 

 

Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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's avatar
    Ashish_Mathur
    1 year ago

    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.

     

2 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    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.