Currency conversion totals not correct

[PBIX_COACH]

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]

)

[EylesIT · Accepted solution]

@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?

[PBIX_COACH]

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?

[EylesIT]

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

[EylesIT]

@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]
    )
)

[PBIX_COACH]

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

[EylesIT]

@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.