The data is stored in a certain currency and you would like to present it in the dashboard converted to different currencies.
As a designer, I would like to have a dashboard filter of currencies in order to switch between the filters.
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:
- You have only one currency managed in the data (for example: everything is in USD)
- 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.
|Date||Service_ID||Revenue in USD|
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.
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.
I recommend you to Star this formula in order to continue using it in other widgets as well.
Now you can create other widgets with his formula and change the currency using the filter
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.