Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating a measure that includes a lookup

Hello,

 

I am trying to create a measure that multiplies the total amount invested (plush a cash balance)  by a % limit based on another table.  The first image is the measure I've created that shows the total amount invested plush the cash balance, the second image is a section of the table I am trying to reference.  In excel I would just do *vlookup(issuer, Limit table, column 2, false) but I do not know how to do the same thing in power BI

 

thanks in advance

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

     I figured it out.

     

    The following code was what I needed:

    Available to invest = (CALCULATE(SUM(Data[Amount]),ALL(Data[Issuer]))
     
    +'Cash Balance'[Cash Balance Value])
     
    *LOOKUPVALUE ( 'Approved Names'[Limit], 'Approved Names'[Issuer], 'Measures List'[Selected Issuer])
     
    thank you

7 Replies

  • Syk's avatar
    Syk
    Resident Rockstar
    4 years ago

    Is that first measure giving you what you want or do you want this to be per issuer? Also, you should be able to reference other columns given that the tables are related. Can you share a screenshot of your relationships?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Sykthe first measure is giving me the total portfolio size (plus cash balance), I would like then to multiply that by a given % based on the approval limits table shown below.  So for example, AAPL may have a 10% limit so I want the number to be 13.4 million and AMCR may have a 5% limit so I want that to be 6.7 million.  The relationships are shown below, issuer is the common field, and is one to many as there is only one listing of an issuer in the approval limits table, but there may be many entries of any given issuer in the data table

      Thanks for your help

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Ashish_Mathur  I do not know how to do that.  Please direct me to where I can see how that is done.  Thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     I figured it out.

     

    The following code was what I needed:

    Available to invest = (CALCULATE(SUM(Data[Amount]),ALL(Data[Issuer]))
     
    +'Cash Balance'[Cash Balance Value])
     
    *LOOKUPVALUE ( 'Approved Names'[Limit], 'Approved Names'[Issuer], 'Measures List'[Selected Issuer])
     
    thank you
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    the measure selected issuer is this 

     

    Selected Issuer = SELECTEDVALUE ( Data[Issuer] )