Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Look up value in same table

Hi there, 

 

I have a table which consists of people (Contact ID), their associated organisation names (Org Name) and billed value (Billed £). There are multiple organisations/rows per contact ID. I am looking to achieve the following - "show the 'organisation name' where the billed is the maximum value billed for the contact". I want this to show as a 'Main Organisation' column against all rows for that contact as per the below.

 

Contact ID   Organisation Name    Billed (£)       Main Organisation

1                  ABC Limited                £10,000        ABC Limited

1                  123 Limited                 £1,000          ABC Limited

1                  XYZ Limited                £0                 ABC Limited

 

I would appreciate any suggestions on this. 

 

Thank you

Need Help

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    Anonymous 

    pls try this

    Column = 
VAR _max=CALCULATE(max('Table'[Billed]),ALLEXCEPT('Table','Table'[Contact ID]))
return maxx(FILTER('Table','Table'[Contact ID]=EARLIER('Table'[Contact ID])&&'Table'[Billed]=_max),'Table'[Org Name])

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, 

       

      Thank you for this, much appreciated. 

      It is informing that there is a problem with the RETURN syntax. I can't quite get this to resolve. 

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        2 years ago

        did you create a column or a measure? could you pls share the error?