This week, we at 1-Answer Analytics began discussions with a large attraction company that has multiple attractions in multiple countries generating revenue in multiple currencies. We were able to highlight one of the features of Power BI is the ability to convert currencies, with proper formatting, seamlessly showing a visualization that adapts to whichever currency they choose. For a company that generates millions of dollars in revenue and expenses, having a single pane of glass that shows all financials in one currency is a huge time saver.
If you have a business with branches in several countries, this is a very helpful tool. For example, if your company owned 50 McDonalds located in 12 different countries, a single dashboard that could show daily sales reports all converted to one currency. This would provide more actionable information than simply showing sales in each currency.
Properly developing the correct dashboard can be a challenge. Here are some of the steps you will need to take. Import current Exchange Rates by using a URL. One we would use is – www.xe.com. Using web data is needed, because this solution is not hardcoded but needs to be refreshable as you to grab the latest exchange rates. After Power BI imports the html charts from the website of your choice, you will need to transform the data removing any extra columns or sections that are unneeded.
You should also want the proper currency symbols and Currency Codes (three letters). The Exchange Rate table you are connected to may or may not have those symbols and codes clearly labeled. One website that can easily provide all the currency symbols and codes is www.theforeignexchange.com or another is www.justforex.com . There should be minor transforming of the data. Use the Merge Query option to bring the currency symbols into Exchange Rate table. Depending on the Exchange Rate website you choose, the default conversion may be to the Euro. For our clients, we would change the Exchange Rate to default to the USD (U.S. Dollar).
When you are transforming the data, you may wish to select only those currencies you generate revenue in. No need to have Exchange Rates for 35 currencies if you only have operations in three countries. However, you may expand your operations, and may want those other currencies available. It is easy to have all currencies in the data and then filter under Values to only those three currencies you regularly need.
The great value is in the dynamic aspect of the data. This allows the formula to change when the exchange rate changes. To get the dynamic quality, you will need an external tool called Tabular Editor. It is one of the best ways to develop the needed calculation groups. If you are not familiar with downloading external tools, or how to ensure dynamic qualities to your data, a Power BI consultant, like those of us at 1-Answer, can be contracted to build this very quickly.
One important tip to remember, there are some currencies that use a dot (period) in the currency symbol. One example is Libya’s currency. Another is the Russian Ruble (though it is the colloquial symbol with the period and not the more formal symbol). That dot will be read as a period, and not part of the currency symbol, in the valuations Power BI creates. This will throw off your data. You will need to manually change that currency line to ignore the dot.
Another tip – You will want to reference the Country Currency Code (three letters) as well as the currency symbol. The dollar symbol “$” is used by many different currencies (Canadian, Australian, Bahamian, Cayman, etc), but each has a different Currency Code. Some Currency Codes look similar, so confirm that you have the correct currency by referencing the currency symbol as well.
As the world’s economies become more intertwined and businesses grow into foreign markets, having Power BI to provide easily understood revenues and costs transformed into one currency will provide analytic insights that successful companies need. This great feature of Power BI may not be a benefit to your company in the present, but it may be exactly what you will need in the future.