I am creating a dashboard displaying the total sales in US$ for different countries. Our database has all the currency rate except US (Rate=1). For some country (e.g. China), people purchase products in Hong Kong dollar, USD and EUR for some reason. I am going to use expressions to calculate the total sales but I got stuck. Can anyone help me out? Thank you so much!
You need a currency conversion table which converts all the values into one standard currency. I'd suggest either linking this currency conversion table with main Fact table or you can use Mapping Load & Applymap() to extract the conversion field. Once you have the values in one standard currency then you perform aggregation on this field.
Thank you for your quick reply. We have a currency conversion table contains the conversion rate into US Dollars. But for US Dollars, we do not have Rate=1 to associate US Dollars converted to US Dollars. So when one country's transactions contains both US dollars and non US dollars (e.g. China), I do not know how to use the expression to calculate. Can you help me out?
Please see the below table. The main problem here is there is no Currency Rate for US Dollars. So for countries like China who has multiple currencies including USD, If it is not including USD, it's going to be simple (just use sum(Sale/Rate)). But when it includes USD, I am not sure how to sum the sales up and convert it into US Dollars. Thank you so much!
.png "Luminary Alumni"){kind=icon}
