Dynamic Currency Conversion in Power BI with DAX

Requirement

A common scenario in multinational companies is having to integrate different currency rates across transactional data.

Quite often sales data is stored regardless of the currency, so the currency code related to each sale is also stored in another column.

So, what we need to do is to integrate the whole daily historical exchange rates for every single currency used within the company.

Then a common approach is to convert through the ETL process all the sales data into one single currency usually USD obviously it can vary from one company to another. This is always a good practice to hold only one single currency in the DWH as it makes it much easier to apply all the business calculation rules.

Let’s assume that in our case we convert all the sales into USD.

However, large international companies have offices and firms all over the world and thus need financial dashboards and reports to be in their different offices’ local currencies.

So, one solution can be to create a dynamic currency conversion measure in Power BI so we can display the USD amount into any needed currencies.

Below I outline how we can solve this particular requirement in a few simple steps:

Modeling

The fact CurrencyRate stores the daily historical exchange rates .
The dimension Currency links the currencies to the fact Currency Rates.
The dimension links the fact Currency Rate and the fact Internet Sales with the date attribute.

Note that if you want to hold and handle multiple currencies in your DWH you’ll need to duplicate the dim Currency, one used as a filter (with a direct link to the fact) and another as conversion calculator like in our scenario (with no link to the fact Sales)

DAX Measure

SalesAmountCurrency = 
   
var _USDMeasure =sum(v_FactInternetSales[SalesAmount])
return
if(ISBLANK(_USDMeasure),blank(),
    if(HASONEFILTER(v_DimCurrency[CurrencyName]),
-- the conversion calculation starts here 
-- the above part is just used to handle null values
-- and slicer with multiple values allowed
        var _sumx =sumx(
        CROSSJOIN(
                    values(v_DimCurrency[CurrencyKey]),
                    values(DimDate[DateKey])
                ),
            CALCULATE(
            var _USDMeasurex =sum(v_FactInternetSales[SalesAmount])
            return
            Divide(_USDMeasurex,VALUES(FactCurrencyRate[AverageRate]))
            )
        )
        return 
        _sumx,_USDMeasure
    )   
)

So this measure simply computes the conversion calculation row-by-row and as you will see in the result section the conversion will be calculated on the fly as we switch from one currency to another.

Result

As you can see the sales amount gets automatically converted into the selected currency.

Make things look nicer

We can also use the above measure and concatenate the currency code with Sales Amout to automatically display the code currency with the sales amount

SalesAmountCurrencyCode = 
   
var _USDMeasure =sum(v_FactInternetSales[SalesAmount])
return
if(ISBLANK(_USDMeasure),blank(),
    if(HASONEFILTER(v_DimCurrency[CurrencyName]),
        var _sumx =sumx(
        CROSSJOIN(
                    values(v_DimCurrency[CurrencyKey]),
                    values(DimDate[DateKey])
                ),
            CALCULATE(
            var _USDMeasurex =sum(v_FactInternetSales[SalesAmount])
            return
                Divide(_USDMeasurex,VALUES(FactCurrencyRate[AverageRate]))
            )
        )
        return 
        if(ISBLANK(_sumx),
            blank(),
            concatenate(CONCATENATE(FIRSTNONBLANK(v_DimCurrency[CurrencyAlternateKey],1), " "),FORMAT( _sumx, "Standard")  )
        ),concatenate("USD ",FORMAT( _USDMeasure, "Standard") )
    )   
)

Note that the format type of this measure is now text.
Therefore, we can no longer simply reference this measure in another measure that would require advanced calculation such as ratio, YTD, projection and so on.
Also, if your users use Excel or want to export the PBI visual into Excel, it won’t work as the figures are in text format.

The sample data used in this post is from AdventureWorksDWH2014.

Power BI – Create Custom Interactive Visuals with R

Prerequisites

Install node.js: Download and Install the latest version of node.js from  the node.js website

Install R : Download and install the latest version of R from here.
Once R is installed I also recommend you to install R Studio the integrated development environment for R. 

Enable R scripting in Power BI: In Power BI desktop, go to File > Options and settings > Options>R Scripting: Then set up your R home directories and R IDEs.

Set up Node js

Open a CMD command and type:

npm install -g powerbi-visuals-tool

To check if the Powerbi-visuals-tools has been installed successfully installed type:

pbiviz 

Let’s build our first R Custom Visual with HTML output

Now using the windows “cd” command navigate to the specific folder where you want to store your custom PBI visuals and type:

pbiviz new sampleRHTMLVisual -t rhtml

You should now see a similar folder named sampleRHTMLVisual created in your specified folder

If you open the file Script.r you’ll see the below R script generated.
You can find this template from the Microsoft github PowerBI-visuals

source('./r_files/flatten_HTML.r')

############### Library Declarations ###############
libraryRequireInstall("ggplot2");
libraryRequireInstall("plotly")
####################################################

################### Actual code ####################
g = qplot(`Petal.Length`, data = iris, fill = `Species`, main = Sys.time());
####################################################

############# Create and save widget ###############
p = ggplotly(g);
internalSaveWidget(p, 'out.html');
####################################################

You can change this script later to create your own visuals. I shall write soon another article about how to create nicer and more advanced visuals in R.

Generate the PowerBI visual file:

Now we need to create the pbi file, still in the CMD command using “cd” navigate to the folder where the script.r is stored and type:

pbiviz package

Once the command has been executed you should see the PBI file in the “dist” folder:

Import the custom visual into PowerBI

Now in PowerBI in the visualizations pane we need to click on the three dots > Import from file and select the PBI file located in the “dist” folder.
Then even though the R script references a sample dataset and not the Power BI data we need to add a measure to the Values area to trigger the visual to appear.

Note that the interactions with this visual are limited as this is an HTML
visual. The main limitation is that you cannot interact with the other visuals present in your PowerBi page.
However we still get few interactions such as:

  • tooltip
  • zoom in/out
  • auto scale
  • display/hide categories
  • compare data on hover…

If you are already proficient in R you can easily create other visuals you just need to edit the script.r file, run the pbiviz package command again and import the generated PBI file.

I’ll be shortly writing another post on how to create more advanced visuals and how to use data directly from PowerBI instead of a preloaded sample dataset.

Also, if you’re fluent in JavaScript and Json you can create even more advanced custom visuals that can fully interact with your other visuals just like the built-in powerbi visuals.

PowerBI – Dynamic Chart Title

Unlike Qlikview, the chart titles in PowerBI can only be static. as you can only pass a static text in the title parameter.

Capture

However, there’s a way around it!
The workaround I found is pretty simple you just need to fake a title by creating a measure which contains your title expression and drop this measure into a Card visual .

Then by applying the same transparency and colours of your chart you just need to turn off the chart tile and put the Card visual on top of your chart.

Here is the code for my  title measure:

 MyMeasureTitle = ("Total Cost of the Top " & [TopN Value] & " Depts VS all other Depts") 

Capture

So my title will interact with the above slicer dynamically however if no values are ticked off I still want a default value to be returned so here is the code for this (you might not need to implement it)

TopN Value =
IF (
        HASONEVALUE ('TopN Filter'[TopN])
         , VALUES ('TopN Filter'[TopN])
         , 10
  )

So after dropping your measure into a Card visual you’ve got your title ready!

Capture

And this how it looks when you place it right above your chart:

Capture10

Make sure your chart and the card have the same size and colour and by setting the right location x,y it will look like the embedded chart title.