This tutorial will focus on currency conversions and how to format these converted currencies using DAX in Power BI. You may watch the full video of this tutorial at the bottom of this blog.
In this example, Sales will be used to run a report based on different currencies from World Wide Importer’s data.
Assumptions Used
These are the assumptions that you should remember in this remodelling:
Creating The Currency Table In Excel
For the Currency Rate Dimension table, Microsoft Excel will be used.
You can see 19 various currencies shown in the Excel table:
The Format column is used to pick a currency that a user selects. The Leading Text column specifies the currency. The Sort Order column manages what the user wants to see first.
If you bring this table into Power BI, it lands in the Dim Currency Rates table.
You can sort the currency table by Sort Order instead of having an alphabetical arrangement. Here’s how to do it:
First, click the header of the column and then change it to Sort Order.
The ticker in Dim Currency Rates table must match the ticker in Fact Currency Rates table.
This is important because in the data model, the Dim and Fact Currency Rates table must be related based on the ticker.
In previous tutorials, you had a Sales measure that converted all the Sales based on the chosen currency.
Now, looking at the table, all Sales were converted to various currencies. You can also see that the Total column is erroneously summing up all the Sales value which is wrong since different currencies can’t be summed up.
This table’s format is not helpful for users since it’s confusing.
The Total Sales measure must be formatted appropriately. It must follow the format from the Excel file that was imported. This is what it should look like:
The Converted Sales and the Formatted Sales have the same numerical value, but the latter looks better with the proper currency symbols. So, you need to remove the Converted Sales since it’s unnecessary.
DAX Code For The Formatted Sales Measure
Next, create the DAX code.
The first variable in the measure, MeasureToFormat, can be dynamic. It correctly converts and formats what the user wants to see, such as Sales or Profit.
The Currency Selected measure was used earlier. It is a SELECTEDVALUE coming from the Dim Currency Rates table.
If you take off Full Name in the Fields’ Column, and select more than one currency, the Formatted Sales defaults to USD because it doesn’t know what to convert to with only one measure.
Looking back at the DAX formula, the next two variables are two LOOKUPVALUE functions put together. One is for the Format and the other for the Leading Text.
The Leading2 variable is for the Leading Text in case there are any.
In the FormatedMeasure variable, you should use the FORMAT function with MeasureToFormat, and then format it based on the LOOKUPVALUE.
This measure is taking the converted Sales, finding what currency you want to convert it to, going to the currency rates tables, and finding the format.
Also, look for Leading Text just in case there are any.
That’s how you achieve the Formatted Sales.
Now, go back to the Formatted Sales measure. Take out the IF statement because it’s unnecessary. All you need is FinalFormat.
After that, drag the Full Name into the Row section and you’ll see the Total Sales formatted in different currencies.
If you click the expand button, you can see all the Sales in that currency.
Fixing Blank Data In The Sales Table
Check if there is blank data in your sales table. These are entries that only show the currency with no amount.
If you find blank data or entries, go back to the Formatted Sales measure and add this variable and IF function:
Once that’s done, you won’t see blank data in your table anymore.
***** Related Links *****
Multiple Currency Logic In Power BI – A LOOKUPVALUE Example
Power BI Exchange Rates: An Update To Multiple Currencies Management
Implementing DAX Measure Groups Into Your Reports – A Power BI Modeling Review
Conclusion
With these steps, your currency conversion in Power BI is complete.
In this tutorial, you just put together LOOKUP functions, different user selections, formatting measures, and a bit of data modelling to appropriately format the currency of your Sales.
This method can be used in currency conversions in Power BI to clear up your sales table and make the presentation better for you and the users.
All the best,
Nick
[youtube https://www.youtube.com/watch?v=MQAXL_xkO_s?rel=0&w=784&h=441]
