Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
HI
Has anyone got another solution to this issue as i still cannot get it to work.
Thanks
Mark
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |