Working with Multiple Currencies

On this page you will find a guide to working with multiple currencies inside your Valsight model.

Getting started

First of all, you need to chose a base currency. We will use EUR (€) in our example. 

The next step is to create a new dimensions. The dimension (e.g. Currency) should contain all the currencies, so all foreign currencies and your base currency. Remember to add a "Country" dimension containing the different countries.

Afterwards, we'll create our currency base data. To do so, you can go ahead and create an excel file containing 2 worksheets: One for the exchange rates (e.g. Exchange Rate) and one to assign countries to the different currencies (e.g. Country).

The "Exchange Rate" worksheet should contain the exchange rates towards your base currency.

Currency OutputCurreny Exchange

The "Country" worksheet should contain the countries on the left followed by the currency in the middle. On the right there should be a value column (Value = 1), so that Valsight can properly load in the data from this worksheet.

Country Currency Value

Now we need to load this base data into Valsight. Therefore we create a new data source (e.g. Currencies) and upload our excel file.

Modelling different currencies

Now that we imported all the data into Valsight, we can start implementing it into our model.

To do so, we first need to create 3 nodes which are linked to our base data. 

The formulas in your specific model may vary due to different naming

Because we want to simulate different currencies, we can not simply always select EUR as our unit. To switch bitween the different currencies, we need to create a new Unit (e.g. diverseCurrencies (divCncy)). 

For more information see Configure Units.

Node Formula Use
Countries-Currencies DATA("Currencies", "Country", "Value") Link the currencies to the countries
FX-Base DATA("Currencies", "ExchangeRate", "Exchange Rate") Import exchange rate for base currency
FX-Simulated 1/DATA("Currencies", "ExchangeRate", "Exchange Rate") Import exchange rate

For the 'Countries-Currencies' node, we chose Quantity ("Qty") as a unit. With help of the "Combined Unit" function, we are able to define the desired units for each node, also shown in the last picture below.

With these nodes we can create a node (non-simulated) containing all exchange rates to our base currency, containing the "Currency" and "Country" dimension.

Node Formula Dimensions
Rates-Real 'Countries-Currencies' * 'FX-Base' Currency, Country,

Let's assume we upload our base data (e.g. revenue) with the revenue in our base currency €. The node is named BaseData and its dimension is country.

Node Formula Dimensions
BaseData DATA(DataSourceName, TableName, MeasureName [, Unit ]) Country

Country Totals
DEU 239.00
GBR 349.00

To obtain the corresponding values in the currency of the specified country we do as shown below,

Node Formula Dimensions
BaseData-Foreign 'BaseData'*'Rates-Real' Currency, Country

This way, only the country based currencies remain. 

Country Currency Totals
DEU EUR 239.00
GBR GBP 310.61

Similarly, if the revenue is available in the foreign currancy, we can use the same approach to transform it into our base currency. 

To recieve the revenue in the unit EUR we use the DROPLEVEL command.

Node Formula Dimensions
BaseData-Simulated DROPLEVEL('BaseData-Foreign'*'FX-Simulated',"Currency") Currency, Country

Country Totals
DEU 239.00


This way, you can use the currency logic either to transfer data in your base currency into different local currencies, or calculate the values of foreign currencies in your own defined base currency.

Below you can see an example of a model for the discussed scenario.

Was this helpful?

Yes | Somewhat | No