Forum Discussion
Currency conversion totals not correct
- 2 years ago
I've looked at this a bit more, and have another idea. Your measure starts with this IF expression:
IF(ISCROSSFILTERED ('Power BI - NOT' )...I think that when you are not filtering for USD i.e. looking at the whole table unfiltered, then this IF is false, so the ELSE part is executed, which returns [Sales Amount] for every cell. So I think try changing your measure to this:
Sales Currency (CAD) = SUMX( VALUES('Power BI - NOT'[Country]), VAR SelectedCountry = 'Power BI - NOT'[Country] VAR DatesExchange = SUMMARIZE ( 'FX Exchange Rates', 'FX Exchange Rates'[MonthYearLong], 'FX Exchange Rates'[EXRate] ) VAR Result = IF ( SelectedCountry = "USA", SUMX ( DatesExchange, [Sales Amount] * 'FX Exchange Rates'[EXRate] ), [Sales Amount] ) RETURN Result )Note that the your measure only applies currency conversion into CAD when the country is USD. Other countries such as INTL do not have any currency conversion applied. Is this what you intended?
I've looked at this a bit more, and have another idea. Your measure starts with this IF expression:
IF(ISCROSSFILTERED ('Power BI - NOT' )...
I think that when you are not filtering for USD i.e. looking at the whole table unfiltered, then this IF is false, so the ELSE part is executed, which returns [Sales Amount] for every cell. So I think try changing your measure to this:
Sales Currency (CAD) =
SUMX(
VALUES('Power BI - NOT'[Country]),
VAR SelectedCountry = 'Power BI - NOT'[Country]
VAR DatesExchange =
SUMMARIZE (
'FX Exchange Rates',
'FX Exchange Rates'[MonthYearLong],
'FX Exchange Rates'[EXRate]
)
VAR Result =
IF (
SelectedCountry = "USA",
SUMX (
DatesExchange,
[Sales Amount] * 'FX Exchange Rates'[EXRate]
),
[Sales Amount]
)
RETURN Result
)
Note that the your measure only applies currency conversion into CAD when the country is USD. Other countries such as INTL do not have any currency conversion applied. Is this what you intended?
- PBIX_COACH2 years ago
Helper II
Thanks so much...your formula is working correctly and is giving me the correct output. Also, that is correct it excludes INTL but if in the future I need to include it would I just add a OR clause to include it?
- EylesIT2 years ago
Resolver II
That will depend on how the 'FX Exchange Rates' and your data model is set up.