Creating a Currency Conversion Filter in the Dashboard

[intapiuser]

 Analytical Need 

The data is stored in a certain currency and you would like to present it in the dashboard converted to different currencies.

 Modeling Challenge

As a designer, I would like to have a dashboard filter of currencies in order to switch between the filters.

Solution

How to manage multiple currencies in one dashboard?

Many times you would like to present the values in the dashboard in different currencies. 

How to manage this depends on how your data is manged:

1. You have only one currency managed in the data (for example everything is in USD)
2. You have all the different currencies in the Data

Here are the different design options:

1. You have only one currency managed in the data (for example: everything is in USD)

In the example below, we want to calculate the service revenue per day.

Your basic data contains the revenue in USD of each service per day.

The best way to manage the conversion rate is by adding a currency conversion table in the ElastiCube

There are many services that provide a service of conversion rate data. 

In the example below I used a free conversion rate data in XML that provides daily data but you can use any other XML conversion rate data. In order to save the data you will need to connect this with Sisense XML connector and build this accumulative built in the Elasticube.

You can also use REST API currency conversion.

The Elasticube model contains your Revenue in USD data (The table above) and connected to the Currencies table using the Conversion Date as key.

  

In the dashboard make sure the currency filter is set to singe selection (There are multiple currencies in the table, you can limit it to less currencies by using a background filter or in a custom table)

In the values presented, you will need to create a formula for (Avg Exchange Rate * Total Revenue) and then when you switch the dashboard filter the value will automatically be affected.

Star this formula

2. You have all the different currencies in the Data

You will need to change the modeling of the table in order to be able to filter in the dashboard according to the currency.

Your data looks probably like this:

In order to filter on the currency type we need to hold all currencies in the same column and also to add an additional column with currency description. This can be done in a custom SQL table in the Elasticube using the Union function to union the different currencies under the same column.

This should be the table's syntax: 

Select Date, Service_ID, Revenue_in_USD as Revenue , 'USD' as Revenue_Currency

union all

Select Date, Service_ID, Revenue_in_Euro , 'EUR'

union all

Select Date, Service_ID, Revenue_in_INR , 'INR'

Your data now should looks like this:

Now you can add a filter in the dashboard and switch between the different currencies.