Tableau Version: 9.1.1 and above
Google Sheets: 10.0 and above
Web Data Connector: 9.1.1 and above
Historically, currency conversion is best advised to be architected in your backend data source. While this works well in situations where you are utilizing live connections with a high performant data base, how would you accomplish this with an extracted data and still allow the user to dynamically change the currency displayed?
Google Sheets has a function within Google Finance called Currency that will perform currency conversions for you through their API. The below formula is converting the target currency, Column A, from USD within Google Sheets. We can leverage the Google Sheets connector and data blending to do dynamic currency conversions without any backend/data source manipulation.
Steps to complete currency conversion with Google Sheets:
1. Build a sheet in Google Sheets similar to THIS or use mine.
2. Build a parameter in Tableau Desktop with all of the currency codes from Google Sheets.
3. Build a new calculation in Tableau Desktop that simply captures the selected parameter value of the Currency selection.
4. Connect to Google Sheet referenced in Step 1 above to build a new data source.
5. Edit Relationships between your primary source and the Google Sheets data source.
6. Match the Currency field from Step 3 above with the Code in the Google Sheets data source.
7. Now, create new calculated fields for each of the measures you wish to convert to the target currency. Here is an example of profit below. We are calculating Profit from the primary source by the target currency conversion from Google Sheets data source.
8. Simply add the Currency parameter to your dashboard to give your users to flexibility to change the displayed currency.
Important Items to Note:
- Make sure you remove symbol prefixes like $ and £
- As a best practice, make sure to call out what currency is being shown in the dashboard, so there is no confusion. IT NEEDS TO BE UNMISTAKABLE AND OBVIOUS.
- Performance on the blend will be EXCELLENT. We are only returning a single row from the Google Sheets data source.
- The target currency conversions will be updated real-time as updated within the Google Finance API.