Forum Discussion
Currency conversion and division error
Hi GraceTCL ,
You don't have the same granularity, on the exchange rate you have a value once a month and your data you have weekly values, altough they are all in the same month when you do the filtering of the table you can notice that the values for the weeks were the date does not match the end of the month you don't have results.
The image below comes from DAX studio and has you can see the result of the table is a empty value for the exchange rate:
If by other and you change the table to the month and year you get results however this needs to be picked up from the one side of the relationship in this case the exchange rate however the total is still zero:
An important part of the calculations is also the direction of the relationships in your case you have one to many from the exchange rate to the date table, if you use the date table you are using the many side of the relationship and you calculation will break.
If you use the formula I have sent (see example below adjusted to your file) the calculation is performed correctly:
Promo Cost USD V2 = VAR DatesExchange = SUMMARIZE ( 'Exchange Rate', 'Exchange Rate'[end_date_id], 'Exchange Rate'[exchange_one_usd] ) VAR Result = SUMX ( DatesExchange, DIVIDE ( [Promo Cost LC], 'Exchange Rate'[exchange_one_usd] ) ) RETURN ResultContext is everything in measures and it comes from the tables themselves, passing trough the relationships and going into visualizations and filters. In this case your issue is granularity and relationships.
Hi MFelix
Thanks for your reply.
For this: "the calculation provided by DAXPATTERNS you add a column to the summarization of the Main table with the exchange rate" I notice that too.
However I still dont understand why it doesnt work as I am summarizing the data in FoodMetrics table to the same month level granularity as the exchange rate table.
Are you able to advise if there is something that I need to change in the formula that that link? Thank you!
Hi GraceTCL ,
You don't have the same granularity, on the exchange rate you have a value once a month and your data you have weekly values, altough they are all in the same month when you do the filtering of the table you can notice that the values for the weeks were the date does not match the end of the month you don't have results.
The image below comes from DAX studio and has you can see the result of the table is a empty value for the exchange rate:
If by other and you change the table to the month and year you get results however this needs to be picked up from the one side of the relationship in this case the exchange rate however the total is still zero:
An important part of the calculations is also the direction of the relationships in your case you have one to many from the exchange rate to the date table, if you use the date table you are using the many side of the relationship and you calculation will break.
If you use the formula I have sent (see example below adjusted to your file) the calculation is performed correctly:
Promo Cost USD V2 =
VAR DatesExchange =
SUMMARIZE (
'Exchange Rate',
'Exchange Rate'[end_date_id],
'Exchange Rate'[exchange_one_usd]
)
VAR Result =
SUMX (
DatesExchange,
DIVIDE (
[Promo Cost LC],
'Exchange Rate'[exchange_one_usd]
)
)
RETURN
Result
Context is everything in measures and it comes from the tables themselves, passing trough the relationships and going into visualizations and filters. In this case your issue is granularity and relationships.