Currency Conversion and Exchange Rates in Tableau

Currency conversion comes up relatively frequently in conversations about Tableau, but there’s not a ton written about it specifically for Tableau, because the solution is actually a generic solution for currency conversion with relational databases.

The main thing to remember with exchange rates is that they change over time, and therefore need to be stored with a Date field. Any join to the fact data you have must JOIN on the originating currency and the date of the transaction. Some organizations may have specific rules on the exchange rates they use over a specific date range — I’ll be showing a daily example, but obviously if you calculate your exchange rates monthly for reporting purposes, the same concepts will work.

The essence of the solution is to JOIN in an exchange rates table to your data. Let’s say I am an North American company which exports a range of products for the European market that are less frequently purchased by North Americans.

We’ve always been a bit Anglo-centric, so our sales to Great Britain are equivalent to those in the rest of the European Union. We’ve lucked out and actually are selling the exact same quantity day to day and in both currency zones.

Date Product Price Quantity Sales Currency
6/21/2016 Tweed Jackets 100 5 EUR
6/21/2016 Tweed Jackets 100 5 GBP
6/22/2016 Miniature Cars 15000 20 EUR
6/22/2016 Tiny Horses 15000 2 EUR
6/22/2016 Miniature Cars 15000 20 GBP
6/22/2016 Tiny Horses 15000 2 GBP
6/23/2016 Upper Lip Stiffener 20 1000 EUR
6/23/2016 Upper Lip Stiffener 20 1000 GBP
6/24/2016 Tweed Jackets 100 5 EUR
6/24/2016 Miniature Cars 15000 20 EUR
6/24/2016 Tweed Jackets 100 5 GBP
6/24/2016 Miniature Cars 15000 20 GBP
6/27/2016 Tiny Horses 15000 2 EUR
6/27/2016 Upper Lip Stiffener 20 1000 EUR
6/27/2016 Tweed Jackets 100 5 EUR
6/27/2016 Miniature Cars 15000 20 EUR
6/27/2016 Miniature Cars 15000 20 GBP
6/27/2016 Tiny Horses 15000 2 GBP
6/27/2016 Upper Lip Stiffener 20 1000 GBP
6/27/2016 Tweed Jackets 100 5 GBP
6/28/2016 Upper Lip Stiffener 20 1000 EUR
6/28/2016 Upper Lip Stiffener 20 1000 GBP

I have to report my earnings in USD to submit them to Wall Street, so internally we do all our comparisons in USD, regardless of the originating currency. To do this, I need a currency conversion table. I brought down the conversion rates for EUR and GBP from xe.com (here’s an example); ideally you’d be keeping the exchange rates in a table in your database and have a process for keeping them up to date.

Date Original Currency USD EUR GBP INR AUD CAD
6/21/2016 GBP 1.47 1.30 1.00 99.35 1.97 1.88
6/22/2016 GBP 1.47 1.30 1.00 99.08 1.96 1.88
6/23/2016 GBP 1.48 1.30 1.00 99.58 1.95 1.89
6/24/2016 GBP 1.36 1.23 1.00 92.52 1.83 1.76
6/27/2016 GBP 1.32 1.20 1.00 89.81 1.80 1.73
6/28/2016 GBP 1.33 1.20 1.00 90.21 1.81 1.74
6/21/2016 EUR 1.13 1.00 0.77 76.27 1.51 1.44
6/22/2016 EUR 1.13 1.00 0.77 76.18 1.51 1.45
6/23/2016 EUR 1.14 1.00 0.77 76.51 1.50 1.45
6/24/2016 EUR 1.11 1.00 0.82 75.45 1.49 1.44
6/27/2016 EUR 1.10 1.00 0.83 74.86 1.50 1.44
6/28/2016 EUR 1.11 1.00 0.83 75.00 1.50 1.45

If you look at rates for the British Pound (GBP), you’ll understand why it is important to look at these rates with a time aspect in place.

Let’s put everything together in Tableau. First, JOIN the main data table to the currency conversion table, with both Date and the original Currency field linking the two tables.

currency conversion joins

Next, you’ll need to create a Parameter which has all of the currencies that can be converted into. Its values should match the currency symbols (EUR, GBP, etc), although you are welcome to give the long names for the display aliases. You’ll use the parameter to determine which rate will multiply against the currency totals. In this example, the calculation adjust the base price of each item. Mathematically, there’s no reason you couldn’t do this against the overall sales, or on aggregates instead of the row level. If you are using extracts, none of this can be pre-calculated anyway because of the parameter’s flexibility, so it’s doubtful that there are performance differences between the different calculations you might write.

Currency picker calc

Now the price needs to multiply against the quantity for the adjusted sales. Any values that need to be adjusted will require a Calculated Field that does an adjustment like this.

currency adjusted sales

Now we can see why currency conversion is necessary to know how much we’re really making (remember, the quantities and prices in the original currency themselves were identical between currency zones and from day-to-day). Looks like quite a bit of our sales in dollars made some kind of exit on the 24th…

Currency declines

Advertisements

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s