Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Sylvain74
Helper III
Helper III

Multi conversions problem

Hi Everyone

 

I need to calculate a weighted average price from an invoice and convert it back to the price unit of the related contract.

For examples:

An invoice of 100'000USD corresponding to 1'000MT of goods, the weighted average will give 100USD/MT but in the contract the price unit was in USC/LB.

Another invoice will have an amount of 25'000 EUR corresponding XXX MT and in the related contract the price unit was EUR/Kg.

The problematic is to convert the unit price back in the price unit of related contract...

 

In my data model, I should have a table storing the conversion factor from USD to USC, EUR to EUR... and another table storing the conversion factor from MT to LB, and MT to Kg.

Till so far it's more or less clear in my mind, but I don't how to retrieve what is the price unit from contract to use to filter out the conversion tables and do the right calculation.

If the weighted average price unit would have been always USC/LC, it would have been simpler...

 

Do you have any idea, suggestions of data model/DAX expression to achieve that?

Thanks

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.