Forum Discussion

MBrackenbury's avatar
MBrackenbury
New Member
8 years ago

Infinity / NaN error

HI   I am using the following statement in a measure which takes the value in the transactional currency and converts it to the local currency.    GBP Invoice COGS Value = sumx('Sales_Invoice_Dat...
edejes2's avatar
edejes2
Advocate IV
8 years ago

Hello MBrackenbury, 

 

This issues its based on the fact that you are using/sign you will need to use the DIVIDE function instead. 

 

Regards!

ED

MBrackenbury's avatar
MBrackenbury
New Member
8 years ago

HI Ed

 

Thank you for the reply however I did try using the divide option previously and couldn't get it to work - possible due to the divide being based on a lookup value.

 

Where in the statement are you saying I need to use the DIVIDE()?

 

GBP Invoice COGS Value = sumx('Sales_Invoice_Data','Sales_Invoice_Data'[Standard Cogs ] / LOOKUPVALUE('CurrencyTable'[Value], 'CurrencyTable'[Date], 'Sales_Invoice_Data'[Invoice Date], 'CurrencyTable'[Rate], 'Sales_Invoice_Data'[Local Currency Code]))

 

Thanks

Mark

  • MBrackenbury's avatar
    MBrackenbury
    New Member
    8 years ago

    HI

     

    Has anyone got another solution to this issue as i still cannot get it to work.

     

    Thanks

    Mark

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi MBrackenbury,

       

      You can try to use below measure:

      GBP Invoice COGS Value =
SUMX (
    'Sales_Invoice_Data',
    DIVIDE (
        'Sales_Invoice_Data'[Standard Cogs ],
        LOOKUPVALUE (
            'CurrencyTable'[Value],
            'CurrencyTable'[Date], 'Sales_Invoice_Data'[Invoice Date],
            'CurrencyTable'[Rate], 'Sales_Invoice_Data'[Local Currency Code]
        ),
        -1
    )
)

      Regards,

      Xiaoxin Sheng