topic Currency conversion totals not correct in DAX Commands and Tips

Currency conversion totals not correct

PBIX_COACH — Thu, 26 Oct 2023 16:34:38 GMT

Hi All,

I'm stuck on this problem where I am able to do the conversion for a single currency however when I try to do a total aggration for the total Sales all converted to CAD I get the incorrect totals. Please see the below screenshot and DAX code.

Any help is greatly appreciated

Sales Currency (CAD) =

IF (

ISCROSSFILTERED ('Power BI - NOT' ),

VAR SelectedCountry =

SELECTEDVALUE('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,

[Sales Amount]

)

Re: Currency conversion totals not correct

EylesIT — Thu, 26 Oct 2023 16:52:05 GMT

PBIX_COACH, are you sure this is not just a display format issue? Your Sales Amount column appears to be formatted to zero decimal places, so it will not display the cents. Whereas the Sales Currency (CAD) column does show cents.

Re: Currency conversion totals not correct

PBIX_COACH — Thu, 26 Oct 2023 16:55:51 GMT

No the problem is the total should not be the same because the formula converts the US to CAD which should be higher. if I place the measure in a card it shows the total without the conversion

Re: Currency conversion totals not correct

EylesIT — Thu, 26 Oct 2023 17:35:57 GMT

PBIX_COACH, I think the problem is that when multiple values exist in the 'Power BI - NOT'[Country] in the filtered context, the measure code returns [Sales Amount] (because SelectedCounty variable is blank, because SELECTEDVALUE('Power BI - NOT'[Country]) is blank).

My suggested solution is to change the measure to group the data up by Country, perform the conversion calculation per country into CAD, then sum up the grouped results. So I think you should try changing your measure to this:

Sales Currency (CAD) = SUMX( VALUES('Power BI - NOT'[Country]), IF ( ISCROSSFILTERED ('Power BI - NOT' ), VAR SelectedCountry = SELECTEDVALUE('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, [Sales Amount] ) )

Re: Currency conversion totals not correct

EylesIT — Thu, 26 Oct 2023 17:54:07 GMT

PBIX_COACH,

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?

Re: Currency conversion totals not correct

PBIX_COACH — Thu, 26 Oct 2023 19:18:24 GMT

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?

Re: Currency conversion totals not correct

EylesIT — Fri, 27 Oct 2023 09:17:56 GMT

That will depend on how the 'FX Exchange Rates' and your data model is set up.