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...
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