Change Currency Symbol Dynamically
In this global world many businesses conduct their operations in different currencies. When they store the transaction they generally store the data as the transaction value in one field then the type of currency in a different field. PowerBI needs the ability to recognize different currencies in the data model so that things like Sum and Average do not conduct those functions across different currencies (it makes no sense to sum a USD transaction with a GBP or EUR transaction).
Please create the ability in the data model to indicate that a field is a transaction amount and that a second field is the currency type. Please then allow this to apply to formatting (put the correct symbol in front of transaction amounts) as well as Sum, Average and other functions.
We also have a separate column that shows the default currency symbol for that business unit that we'd like dynamically appended to the currency fields. Currency short code is also available.
We have experimented with concatenating the currency symbol onto the value but then you can't run calculations on the result as it turns it into a string. Which is confusing for our end users.
Has this been implemented?
I agree with Mat formatting the transaction amount with a currency symbol changes the number to text which makes the value useless for math functions or charting.
Larry Clark commented
Currency intelligence is an increasingly important feature for Power BI and SSAS tabular. We're implementing Power BI for a large retirement fund, and this issue is glaring. Any table visual showing multiple currencies has to be configured carefully, or it won't make sense.
Totally agreed. FORMAT function can help in this case but it converts the value to text, hence visuals will not perform with that value.
Agree 100%. Formatting currency values based on the viewer's locale is a bizarre idea. This lack of multi-currency support is a real oversight.
My proposed solution would be the same as Matt, that is to select a column in the same table which holds the three character currency code.
I have also been down the FORMAT route, and as pointed out, the resulting string is then useless for summations and averages.
As a follow up - formatting the transaction amount with a currency symbol changes the number to text which makes the value useless for math functions or charting.