Forum Discussion

PBIX_COACH's avatar
PBIX_COACH
Icon for Helper II rankHelper II
2 years ago
Solved

Currency conversion totals not correct

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 incorrec...
"DAX"
  • EylesIT's avatar
    EylesIT
    2 years ago

    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?

EylesIT's avatar
EylesIT
Icon for Resolver II rankResolver II
2 years ago

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