Infinity / NaN error

[MBrackenbury]

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_Data','Sales_Invoice_Data'[Standard Cogs ] / LOOKUPVALUE('CurrencyTable'[Value], 'CurrencyTable'[Date], 'Sales_Invoice_Data'[Invoice Date], 'CurrencyTable'[Rate], 'Sales_Invoice_Data'[Local Currency Code]))

The calculation seems to work fine and creates the column OK in the table with the correct data but when I then apply it to a BI report i get infinity errors.

I think i need to change the statement so that it handles a 0 but all the options i have tried failed.

Any help would be appreciated.

Thanks

Mark

[edejes2]

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]

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]

HI

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

Thanks

Mark

[Anonymous]

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