I have a file that has two main tables, one is 'Currency Rate' by date, so it show like this:
And another with 'Invoicing', with many more columns, but one of them is 'Offer Currency' showing which currency was used for each offering.
What I want now is a graph showing the value of all offering in a chosen currency. Currently I've set the variable vCurrency with all posible currencies in a dropdown list and the graph formula is currently like this:
Yes, but that implies adding a few columns and - the most important one - making a limited selection of currencies to which you want to convert. For example EUR, CNY, USD and JPY or something. Then JOIN your conversion rates table to the facts table as many times as you have target currencies. Each JOIN adds a conversion rate from the original currency (can be different for each row) to a target currency.
If you give those embedded conversion factor a field name that contains the target currency, then your ultimate expression for Sales becomes (using names like ConversionFactorToEUR, ConversionFactorToUSD, etc.):
Why not then calculate amounts in different currencies from the start? Because the JOIN used in this technique can be easily embedded in a loop that walks a series of target currencies (in another variable) and adds the necessary Conversion columns to your facts table. Makes this mechanism extremely flexible. The only thing you need to do for getting additional target currencies is provide the conversion rates and add the currency symbols to your configuration variable.