6 Replies Latest reply on Jan 17, 2017 9:36 PM by Hannah M

    Exchange Rate Calc

    Hannah M



      I need to convert a local currency value into USD. I have sample data below in the screenshot. Conversion to USD is the field with the conversion rate. Sales in USD and Profit in USD are the new fields that need to be created.


      Sales in USD = Sales Local Currency * Conversion to USD

      Profit in USD = Profit Local Currency * Conversion to USD


      This could be achieved by creating the above 2 calculated fields with the above formula.


      But since I have about 20 measure fields that need to be multiplied with the Conversion to USD field, is there any other easier way to multiply all measure fields with Conversion to USD field without having to create 20 more calculated fields?


      I would like to show all the fields in the dashboard converted to USD.


      Please shed some light on this one.

      Thanks in advance.




        • 1. Re: Exchange Rate Calc
          Sherzodbek Ibragimov


          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?

          • 2. Re: Exchange Rate Calc
            Jamieson Christian



            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 of 1 people found this helpful
            • 3. Re: Exchange Rate Calc
              Tom W

              +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

              • 4. Re: Exchange Rate Calc
                Sherzodbek Ibragimov


                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.

                • 5. Re: Exchange Rate Calc
                  Sherzodbek Ibragimov


                  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


                  • 6. Re: Exchange Rate Calc
                    Hannah M

                    Thanks Jamieson. I'll try this approach. I think 10.1 would allow a pivot of the data source.