![]() The Sales Currency measure applies the required currency conversion to the result of the Sales Amount measure. Implementing the currency conversion in DAX The requirement is to enable the user to choose a different reporting currency by using a slicer, obtaining something like the following report where the selected reporting currency is EUR. All the amounts are in USD, which is the single currency used to record transactions in the Sales table. The following report shows the Sales Amount measure for the sales of Cell phones in 2008. The complete model also includes other tables such as Product and Customer. The following diagram shows the tables that are relevant to this description. The AverageRate column has the value we consider the average exchange rate of the month that must be applied as a conversion rate to all the transactions included in the same month. There is also a hidden ExchangeRate table that contains the exchange rate for every currency in every month, assigned to the first day of each month. The data model we consider includes a Sales table with all transactions recorded in USD as a currency. An extended description of the three scenarios with other dynamic solutions is included in a chapter of the Analyzing Data with Microsoft Power BI and Power Pivot for Excel book. ![]() The second scenario could be implemented by transforming data so that it is imported in the model in a single currency, moving the challenge over to the very scenario we describe in this article. In this article, we only consider the third scenario, “Data in a single currency, report with multiple currencies”. Therefore, a dynamic solution based on DAX measures and a properly designed data model makes more sense. When you need to create a report in multiple currencies, computing data in advance could be challenging and expensive. Therefore, it is a good idea to solve all the scenarios requiring a single currency in the report by converting all amounts at the time of data import into the data model. The rule of thumb is to apply the currency exchange conversion upfront.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |