Forum Discussion
Power bi( currency)
Dear all, could you please support me with one question . I have two separated tables in Power BI. In one I have product amounts for each county in local currency. In another table I have currency rate for different countries. So firstly I made relationship between them on country colums . So then I want to create new column in first table in which I want transform all local amounts in eur - ( sum_in_eur= sumx( first table ( amount local )*second table ( currency rate). But the result is symbol ∞. What’s wrong?
The issue arises because your formula may be encountering missing or zero values in the currency rate or mismatched relationships. Here's a simplified solution:
- Ensure the relationship between the tables (on the Country column) is correctly set up as one-to-many.U
- se this formula for a calculated column in the first table:
Sum_in_EUR = 'FirstTable'[AmountLocal] * RELATED('SecondTable'[CurrencyRate])The RELATED function pulls the matching CurrencyRate for each country.
Handle missing or zero currency rates to avoid errors:
Sum_in_EUR = IF( ISBLANK(RELATED('SecondTable'[CurrencyRate])) || RELATED('SecondTable'[CurrencyRate]) = 0, BLANK(), 'FirstTable'[AmountLocal] * RELATED('SecondTable'[CurrencyRate]) )Double-check that Country values match perfectly between tables (no typos or extra spaces).
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
5 Replies
Hi
Notice that you have created the relationship, then you can use the RELATED() function.Try the following DAX:
Sum_in_EUR = SUMX( 'FirstTable', 'FirstTable'[Amount_Local] * RELATED('CurrencyTable'[Rate]) )Or you could merge these tables into one.
The issue arises because your formula may be encountering missing or zero values in the currency rate or mismatched relationships. Here's a simplified solution:
- Ensure the relationship between the tables (on the Country column) is correctly set up as one-to-many.U
- se this formula for a calculated column in the first table:
Sum_in_EUR = 'FirstTable'[AmountLocal] * RELATED('SecondTable'[CurrencyRate])The RELATED function pulls the matching CurrencyRate for each country.
Handle missing or zero currency rates to avoid errors:
Sum_in_EUR = IF( ISBLANK(RELATED('SecondTable'[CurrencyRate])) || RELATED('SecondTable'[CurrencyRate]) = 0, BLANK(), 'FirstTable'[AmountLocal] * RELATED('SecondTable'[CurrencyRate]) )Double-check that Country values match perfectly between tables (no typos or extra spaces).
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKSHi Olha_Tseniukh19 , Just checking in as we haven’t received an update from you regarding last response. Were you able to resolve the issue with Poojara_D12 & ChainsawMan 's answer?
If solution was helpful, please mark it as Accept Answer and click Yes to confirm.
Feel free to let us know if you have any further questions—we’re here to help!
Thank you for being a part of the Microsoft Fabric Community Forum!Hi @Olha_Tseniukh19 , Just checking in as we haven’t received an update from you regarding last response. Were you able to resolve the issue with @Poojara_D12 & @ChainsawMan 's answer?
If solution was helpful, please mark it as Accept Answer and click Yes to confirm.
Feel free to let us know if you have any further questions—we’re here to help!
Thank you for being a part of the Microsoft Fabric Community Forum!Hi Olha_Tseniukh19
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
