Exchange Rate total is wrong

[unemployer]

hey there.

I have a table of sales with different currencies, and I made a measure to convert the sales to my desired currency.

the issue is when I view the total it's not correct and I have no idea why.

here is the sales table:

here is the total, which is wrong

here is the measure that calculates the currency exchange

the currency table is just a table that gives me the exchange rate for my currency.

Revenue in SAR = 
VAR    
    _currency= MAX('Backend Revenue'[Currency])
VAR
    _cinversionRate= 
        CALCULATE(
            MAX('Currency'[SAR per unit])
            , 'Currency'[Currency]=_currency
        )
VAR
    _unitPriceSAR=
         SUM('Backend Revenue'[Value])* _cinversionRate 
RETURN
    _unitPriceSAR  

[Jihwan_Kim]

Hi,

I do not know how your semantic model looks like, but please try something like below whether it suits your requirement.

Revenue in SAR =
VAR _unitPriceSAR =
    SUMX (
        VALUES ( 'Currency'[Currency] ),
        CALCULATE ( SUM ( 'Backend Revenue'[Value] ) )
            * CALCULATE (
                MAX ( 'Currency'[SAR per unit] ),
                'Currency'[Currency] = MAX ( 'Backend Revenue'[Currency] )
            )
    )
RETURN
    _unitPriceSAR

[unemployer]

same thing

[DataNinja777]

Hi @unemployer , 

If you don't mind, could I ask the following questions?  Does your fact table have the currency column? In that case, are you using the physical relationship between your currency field in your fact table with the exchange rates table?  Also, exchange rates tends to be reevaluated for different time periods typically every month, and in this case, combination of your fact table currency field with the month information may need to be linked with the currency and month concatenated information in the currency table, I thought. 

Multiplication or division depends on how the currency pair is denominated in your particular currency table data. 

Best regards,

[Jihwan_Kim]

Hi,

Please share your sample pbix file's link, and then I can try to look into it.

[unemployer]

sure, here is the sample data

sample 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure whether the below is what you are looking for, but please check if it suits your requirement.

[unemployer]

you are a legend