Multi-Currency Tables

Get Help
1

What's the best way to handle monetary fields where there are different currencies?

For example a table payments which contains a column currency and a column value.
How can we format the value column based on the contents of the currency column.

Similarly could we create a formula column where the value is multiplied via an exchange rate into a single currency to create aggregates of values from multiple currencies?

2

Hi @LeoAdamek
You cannot currently change formatting based on another column, but there's a request for it:
https://github.com/metabase/metabase/issues/9879 - upvote by clicking :+1: on the first post

You could likely create a single-currency column calculation via Models:
https://www.metabase.com/docs/latest/users-guide/models.html
Alternatively create a View on your database.

3

Hi,

Thanks for the quick reply. I think it might be beneficial to use some views in the database to power the metabase models as it will be easier to optimize the data for this use-case verses the highly transactional model of the table itself.

4

What I decided to do was create a model, that model uses the crosstab function in postgres' tablefunc extension to create a pivot table of the values joined to the original. Here's an example of how that looks:

with t as (
    select * from crosstab('select id, currency, value') from sales 
   as (id text, USD char(3), GBP char(3), EUR char(3) /* Repeat for each currency */)
) select sales.*, t.* from sales right join t on t.id = sales.id

This creates a view which has all the original fields from sales but then has a set of columns for each currency where most columns will be null, but the column for the currency being used will be populated.

This could also be done by selecting the query which creates t and joining sales via the question builder if required.

1 Like