Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Infinity Value appears even though a Value should appear

I'm trying to get the converted currency Cost in Sterling by using a vlookup from my currency file as follows:

 

Cost Price GBP = If(Products[Cost Price Currency]="GBP",Products[Cost Price],Products[Cost Price]/LOOKUPVALUE('exrates-monthly'[Currency units per £1 ],'exrates-monthly'[Currency Code],Products[Cost Price Currency]))
 
However, I've noticed some infinity values with some records where there should have been a value as there is a Products[Cost Price] against these infinity records.
 
How do I go about getting the actual value for these records instead of the infinity sign?
Need Help
Show and Tell

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try like

    Cost Price GBP = If(Products[Cost Price Currency]="GBP",Products[Cost Price],
    divide(Products[Cost Price],LOOKUPVALUE('exrates-monthly'[Currency units per £1 ],'exrates-monthly'[Currency Code],Products[Cost Price Currency]))

    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak

       

      This works perfectly and gets rid of the infinity symbol thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Have you tried DIVIDE in your calculation?

     

    Cost Price GBP = If(Products[Cost Price Currency]="GBP",Products[Cost Price],Products[Cost Price]     DIVIDE       LOOKUPVALUE('exrates-monthly'[Currency units per £1 ],'exrates-monthly'[Currency Code],Products[Cost Price Currency]))
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      The syntax you've provided below is comming back with errors, how can I write this formula using the DIVIDE function?

       

      Many thanks