Forum Discussion
Multi conversions problem
If you want to convert 100'000 USD / 1'000 MT to CAD/LB then you'd do
[100'000 USD * (1.33 CAD/USD)] / [1'000 MT * (2205 LB/MT)] = 0.06 CAD/LBTo set this up in your model, I'd start with the currency conversion patterns:
https://www.daxpatterns.com/currency-conversion/Weight conversions are simpler since they don't change over time.
If you want to convert 100'000 USD / 1'000 MT to CAD/LB then you'd do
[100'000 USD * (1.33 CAD/USD)] / [1'000 MT * (2205 LB/MT)] = 0.06 CAD/LB
To set this up in your model, I'd start with the currency conversion patterns:
https://www.daxpatterns.com/currency-conversion/
Weight conversions are simpler since they don't change over time.
Thanks Alexis, your link makes me on the right track.
In my case there is a kind of currency conversion only to convert USD to USC which is a kind a subcurrency since you always need to multiply by 100.
My main issue was how to retrieve the contract price currency and unit from the dimension but I think I will put them in the fact tables. Like this I will have the weight unit, amount currency, price unit and price currency in the same table and then I will join on conversion tables (price and weight) to calculate the weighted average price.
Thanks again