Have you ever thought about creating another files that contains conversion rates of each currencies to USD and blend them based on Currency ID on both data sources? http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#multiple_connections.html#blend
Will this help you?
1 of 1 people found this helpful
My first recommendation (if this is an option for you) is to pivot your data.
Take all of the 20 measure fields that represent "_____ in Local Currency" and pivot them so that you have just two fields, which you can name "Metric" (and would contain the original names of your pivoted columns) and "Value in Local Currency".
Then, you will only need one calculated field:
[Value in USD]
[Value in Local Currency] * [Conversion to USD]
Pivoting your data means you will construct your views in a very different manner, but it generally does not make the task more onerous. It's more a question of whether Tableau gives you the option to pivot your data source in the first place. (Excel and CSV files can be pivoted in Tableau. Most DBMS data sources cannot.)
Does this help?
+1 to Jamieson's advice on pivoting. It's going to create problems for you in other areas though as it will alter the way to create charts to compare metrics.
I know creating 20 calculated fields sounds like a lot of work, but in the scheme of things you do it once and it's over. You could also look at exporting the connection once done so you don't need to repeat this each time you connect to the file - Export Data Sources
I agree with you as Jamieson got right direction. Looks like I didn't read the requirements thoroughly as I was about the leave my desk thus replied very quickly.
Without looking at actual structure of data source, there are few other options (again depending on your structure of data source) you can deploy.
- Pivot data as Jamieson and Tow mentioned above. As Tom mentioned, pivoting will affect some or all viz on your existing visuals so you may want to play with pivoted value and names and how far you should adjust your current calculated field and views.
- Custom SQL query (Vertical List of Attributes both Pivot and Custom SQL query steps explained).
- Duplicate data source and pivot those 20 measures and blend primary and secondary sources using a common key or keys.
Hope any advise above help you to solve your issue. Thank you
Thanks Jamieson. I'll try this approach. I think 10.1 would allow a pivot of the data source.