on - edited on by
DRay
Analytical Need
There are many cases when we will want to measure a metric by a different angle or definition. For example, in some cases you would like to allow your end users to analyze average duration of a resource utilization in a different time unit (seconds, minutes, hours), count of participants by a different gender (male, female, all), average of product weight in either Lbs. or KG or an average amount of transaction in different currencies (USD, EURO, local).
Let’s assume that you want to analyze for each month, the total amount of your transactions and for your users to select the currency in which they would like to view your dashboard.
Modeling Challenge
Think about a table within your data source which includes a value for each of the amounts. Instead of showing 3 different values on a widget (one for each currency), you might want to consolidate all values into one to show a generic amount. In that case, your user should be able to toggle between the available currencies.
There are existing visualization solutions that provide ways to toggle between different views (Tabber, Switchable dimension, the measure changer) and a modeling solution called the transpose technique (can read more about it on the funnel analysis post). Both addresses the same need from a different angle.
The following solution suggests a different approach which doesn’t involve the use of plugins, more than one widget or duplicating records. As opposed to the solutions described earlier, the following solution is highly recommended when dealing with big data volume or busy dashboards with already many widgets.
Solution
Let’s assume that this is the structure of your transactional table:
As you can see, for each currency we have dedicated column.
For toggle between those values we’ll need a field that will contain the different currencies. For that purpose, we’ll create the following table (either on a flat file or within the EC):
Currency – we’ll use it as the filter within the dashboard.
USD\Euro\Local – Boolean variables to be used within the widget formulas to affect the total amount per the use selection.
1 – random value to connect each of the 3 records in the Currency table with all the records of the Fact table. (Many-to-Many relation won’t apply as on the dashboard we’ll force one selection from the Currency table).
Final step within the EC is to create the same dummy column on the Fact table with the same value (1), by creating a custom column:
And then simply connect between the tables:
On the dashboard side:
- Add a new filter on the Currency field from the currency table and define it as a radio button:
2. On the widget level, the value should contain the following formula:
[Total Local Amount]*[Max Local]+[Total USD Amount]*[Max USD]+[Total Euro Amount]*[Max Euro]So, for each selection of the user, we’ll multiply two out of the three total amounts by 0 and the selected currency by 1. For example, when the user selected the local currency, the formula would look like that:
[Total Local Amount]*1 +[Total USD Amount]*0+[Total Euro Amount]*0 = [Total Local Amount]And when it will be changed to USD it will look like that:
[Total Local Amount]*0+[Total USD Amount]*1+[Total Euro Amount]*0 = [Total USD Amount]And this is how it will look like on the dashboard:
Attachments
