Sales value calculation based on user selected Currency and Year

[msingh2019]

I have a Sales fact table with the following columns - 

Product, Month, Year, Sales Value, BaseCurrency

I have another table 'Exchange Rates' which has the different currencies and exchange rates.

BaseCurrency, TargetCurrency, ExchangeRateYear, ExchangeRate

TargetCurrency and ExchangeRateYear will be selected by the user via slicers. Based on the user selection a measure 'Sales Value in Selected Currency' should be calculated for each product as Divide(Sales Value,ExchangeRate).

The output will look something like this - 

Product, Month, Year, Sales Value, Sales Value in Selected Currency

P1, Jan, 2019, 100, 105

P2, Jan, 2019, 200, 110

The calculation logic should be [Sales Value in Selected Currency] = 'Sales'[Sales Value] / 'Exchange Rates'[ExchangeRate] WHERE 'Sales'[BaseCurrency] = 'Exchange Rates'[BaseCurrency] AND 'Exchange Rates'[TargetCurrency] = [Selected Currency] AND 'Exchange Rates'[Year] = [Selected ExRate Year]. 

I tried using a SUMX function -

Sales Value in Selected Currency = 

SUMX ('Sales', 'Sales'[Sales Value]/LOOKUPVALUE('Exchange Rates'[ExchangeRate], 'Exchange Rates'[BaseCurrency], 'Sales'[BaseCurrency], 'Exchange Rates'[TargetCurrency], [Selected Currency], 'Exchange Rates'[Year], [Selected ExRate Year]))

but I get an error - 

"A table of multiple values was supplied where a single value was expected."

Can anybody help?

[msingh2019 · Accepted solution]

I used the following formula and it worked - 

Sales Value = SUMX(Sales Table,[price]*[Units]*LOOKUPVALUE(Exchange Rate Table[exchange_rate],Exchange Rate Table[local_currency],Exchange Rate Table[currency_id],Exchange Rate Table[target_currency],[Selected Currency],Exchange Rate Table[year],[Selected Exchange Rate Year]))

Note that I have slicers for the user to select the target currency and exchange rate year, based on which the sales value will be calculated. Also, a relationship needs to exists between the Sales Table and Exchange Rate Table for the LOOKUPVALUE function to work. 

[Anonymous]

Hi @msingh2019 ,

I think the formula should be something like below and you can use SELECTEDVALUE() function to get the slicer value.

calculate(sum('sales'[sales value]),filter(table,conditions))/calculate(selectvalue('Exchange Rates'[exchangerete]),filter(table,conditions))

If you don't have any Confidential Information, please consider share some sample data to us.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[msingh2019]

@Anonymous Please see link to the sample PBIX file.

https://drive.google.com/drive/folders/1EbVlRjfayJLI4hnWxjhSrAHo2a-j7QCo?usp=sharing

Thanks.

[msingh2019]

@Anonymous Did you get a chance to look at the sample pbix file that I uploaded?