how to present sales data that can be filtered by currency

[shihwenwu]

Hi all,

I'm new to DAX and power BI

I have written a measure that calcualte the total sales filtered by week (pic1)

What I need to do is to present the sales data into different currency by a currency filter, but I not sure how to write a measure to present that.

at the moment the currency is vary according to its country

below is exchange rate table

below is the data model 

Thx!

[Anonymous]

Are you trying to filter only those transactions that corresponds to country and apply the exchange rate  based on the transaction date (or) something like reporting country so that all transactions irrespective of transaction country would be picked and applying the exchange rate on it?

[shihwenwu]

hi @Anonymous

There will be 2 filters in the final dashboard

one is contry filter and the other is currency filter

The user will be able to check the total sales of certain country(s) based on certain currency.

something like:

The current Mesure has summed up the correct total sales, but in different currency

I am thinking of enhancing the current Measure so that it can get and multiply the correct rate based on "currency" and "weekEnding" in Sales table (which is business_report_P2), and "transactCurrency" and "weekEnding" in exchange rate table.

Here's the current measure:

Jan.13 = CALCULATE(SUM(business_report_P2[ordered_product_sales]), FILTER(business_report_P2,[WeekEnding_textForm] = "1/13/2018"))