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
How can we format the
value column based on the contents of the
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?
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 on the first post
You could likely create a single-currency column calculation via Models:
Alternatively create a View on your database.
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.
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.