Multiple-Multiple Currency Conversion

[LizMor]

Hi all,

I have the below measure to convert my Transactions (or Sales) data from multiple currencies (GBP,USD,ZAR) to multiple currencies (GBP,USD,ZAR,KES,INR,NGN). My code and model is shown below, however, I keep getting the error that more than one currency has been selected, despite having selected just one in the screenshot of my report.

Sales (Monthly) = 
IF (
    HASONEVALUE('Target Currency'[CurrencyCode]),
    VAR AggregatedSalesInCurrency = 
        ADDCOLUMNS(
            SUMMARIZE(
                Transactions,
                'Date'[Calendar Month Year], --maybe just use Month
                'Source Currency'[TxnCurr]
            ),
            "@SalesAmount",[Sales (internal)],
            "@Rate",CALCULATE(SELECTEDVALUE('Exchange Rates - Monthly'[Rate]))
        )
    VAR Result = 
        SUMX(
            AggregatedSalesInCurrency,
            IF(
                NOT (ISBLANK([@Rate])),
                [@SalesAmount]*[@Rate],
                ERROR("Missing conversion rate")
            )
        )
    RETURN Result
)

I think the issue might be pertaining to the fact that I am using one exchange rate for each month, but I have formatted the Exchange rates - Monthly[Date] to only be yyyy/mm, so I'm not quite sure what the issue is.  

Any help would be very much appreciated!

[LizMor · Accepted solution]

Update: On further inspection of the data provided, I see the exchange rates table is missing some conversions. I will rectify this and follow-up here if that does the trick.