I need to calculate a weighted average price from an invoice and convert it back to the price unit of the related contract.
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?