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.

DAX – TREATAS function – Book-to-Bill Ratio use case

Treatas is a powerful DAX function that applies the result of a table expression as filters to columns from an unrelated table.

In other words, treatas applies the filter context of a specific table to another unrelated table (no relationship in the model).

Book to Bill ratio

A book-to-bill ratio is the ratio of orders received vs orders shipped and billed for a specific period, a month in our case.

The book-to-bill ratio reveals how quickly a business fulfils the demand for its products.

How do we do this in DAX?

There’s couple of ways to achieve it:

  • Bidirectional filter + changing the filter context: bad practice will literally kill the performance of your model
  • Intersect + Lookup: was ok before the treatas function but turns to be much slower than treatas
  • Role playing dimension + userelationship function: by far the fastest but you ned to use role playing dimensions
  • Treatas: if like me you cannot use role playing dimensions for whatever reasons this is the best option

OK here is my model:

  • Fact Order
  • Order date dimension
  • Ship date dimension

There isn’t any relationship between my two dimension and the granularity level of my fact table is the date (day).

Power BI Model

Power BI – Orders Received and Orders Shipped

We’ve received 2,457 orders between Jan 2019 and March 2019 and those 2,457 orders have been delivered between Jan 2019 and May 2018.

Power BI – Book to Bill ration for Jan 2019

And here I filtered out only the orders received in January so among those orders only 369 have been shipped the same month which gives the following ratio: Ratio = Orders Received/Orders Shipped = 0.358

OK now remember there’s no relationship between my two date dimensions so how do we propagate the filter context of the Order Date to the Ship Date? This where Treatas comes in.

Power BI – Book to Bill ratio using Treatas function

Propagate the filter context to unrelated table with Treatas

Orders Shipped:
Only the orders shipped the same month as the received order are counted. This is done by propagating the filter context of the Order Date to the Ship Date.

Orders SHipped (treatas) = 
calculate(
                    sum('Fact Orders'[nb Orders]),
                    TREATAS(VALUES('Order Date'[YearMonthnumber]),'Ship Date'[YearMonthnumber])                 
)


Now we can compute the ratio applying the same logic.
Ratio (Book to Bill) = Total Orders Received / Total Orders Shipped

book to bill ratio (treatas) = 

DIVIDE(
calculate(
                    sum('Fact Orders'[nb Orders]),
                    TREATAS(VALUES('Order Date'[YearMonthnumber]),'Ship Date'[YearMonthnumber])
                    
),calculate(sum('Fact Orders'[nb Orders]))
)

In my example, my model contains two unrelated Date tables.
When I apply a filter to Order Date[Year Month] “Jan2019” the same filter, “Jan2019” is applied to Shipped Date[Year Month] hence the two tables must use similar column types such as Date, Product, Location, Customer…


You can read more about treatas in the microsoft docs

DAX – Exclude data based on Slicer selection

How works a slicer in Power BI?

A slicer is just like a filter, it is used to narrow a portion of data shown in a visual.
For example if we want to narrow my dataset to only specific regions we just need to tick the region we want to see and all the unticked regions will not show up in our visual.

So a normal slicer includes the selected items and excludes the unselected items. But what if instead of including the selected items we want to exclude them.

How to exclude data from a Slicer selection?

In other words, we want to use a slicer to exclude some data and display all the others.

Step 1:
First, we need to create a disconnected dimension which will be a copy of the dimension used as a slicer, “DimSalesTerritory” in my case.

Step 2:
We then need to create a measure “isExclude” that will be used to find out which rows should be excluded if any.

isExclude = 
IF(
    MAX(DimSalesTerritory[SalesTerritoryRegion]) IN 
    ALLSELECTED('Disconected DimSalesTerritory'[SalesTerritoryRegion]) 
    &amp;&amp; COUNTROWS(ALLSELECTED('Disconected DimSalesTerritory')) <> 
    COUNTROWS(ALL('Disconected DimSalesTerritory')),
    1,
    0
)

The first condition checks which rows should be excluded and the second condition is only used to prevent the measure to exclude everything when nothing is selected.

Step 3:
Now we can create a new measure “Sales Amount (Disconnected)” which will sum up the SalesAmount for all the regions that are not excluded.

Sales Amount (Disconected) = 
VAR FilterRegions = 
    FILTER (
        FactInternetSales,
        [isExclude] =0
    )
RETURN
    CALCULATE (
        sum(FactInternetSales[SalesAmount]), 
        FilterRegions 
    )

Step 4:
All right, you should be all set up now now.
As we can see on the visuals below when we select Australia from the slicer all the other regions appart from Australia are displayed.

Step 4 bis:
If instead of creating a new measure you want the “Exclude Slicer” to work on your visual for any measures you just need to add the “isExclude” measure into the visual filter pane.

Visual level filters for slicers

Since the latest Power BI release (June 2019) we are now able to filter a slicer based on a measure!
And I think we’ve all been waiting for this feature for quite a while.

One of the top reason we needed this feature was to have the ability to sync slicers. (Well this was already achievable with the bidirectionnel filter which is a really bad practice…)
So as we can see on the example below the customer Alma has bought only three products but the whole list of products is still showing up in the slicer Product.

So how can sync the slicers and make sure only the sold products apear in the slicer?
This is pretty simple we only need to have a measure and not a column and drag this measure into the visual filter area of the slicer.

Once we have dragged the measure into the visual filter pane the slicer will only show the sold products.

And if we want to filter a slicer based on more than one measure we can simply create a measure that will check the not null condition on multiple measures and returns 1 if at least one the measure is not null. And then drag this filterMeasure into the visual filter area to sync our slicers.

filterMeasure= 
if(not(ISBLANK([Order Qty])) || not(ISBLANK([Sales Amount])) ,1,0)

Visualising Deviation From Average in Power BI

Note that throughout this article I will use the margin measure to illustrate the deviation from average application but the same pattern can be applied to any measures like Sales volume, Delivery time, Profit, etc…

Margin is a key metric to asses high level performance of a company.
But sometime we want to measure and compare specific shop or department or employee performance with the entire company over all performance and this where deviation from average metric comes to help.

The requirement are as follows:

  • Company overall margin over time (which is just the margin average)
  • Employee margin over time
  • Employee margin deviation from the company margin average over time
  • Employee margin deviation vs Last Year Employee margin deviation

And the final result should look like this:

Capture2

Now let’s define our measures:


 

Margin  and Overall Margin calculation

Margin:

Margin % = divide(([Total Revenue]-[Total COGS]),[Total COGS])

Overall Margin:

All Margin =
if(
    ISBLANK([Margin %]),
    BLANK(),
    calculate([Margin %],ALLEXCEPT('Fact','Date')
)

Removes all context filters except filters that have been applied to the fact or date tables.

Margin Deviation From Average measures

Margin Deviation From AVG:

Margin Deviation =
var _AllMargin = calculate([Margin %],ALLEXCEPT('Fact','Date'))
Return
IF(ISBLANK([Margin %]),
   BLANK(),
   [Margin %] - _AllMargin
)

Margin Deviation CY vs LY (Hidden):

Margin Deviation CY vs LY (hidden)=
var _MarginDeviationLY = CALCULATE([Margin Deviation],SAMEPERIODLASTYEAR('Date'[Date])
)
var _MarginDeviationCYvsLY = [Margin Deviation] - _MarginDeviationLY
Return
IF(ISBLANK(_MarginDeviationLY),
   BLANK(),
   IF(_MarginDeviationCYvsLY>0,
      1,
      0
     )
  )

Margin Deviation CY vs LY:

Margin Deviation CY vs LY =
IF(ISBLANK([Margin Deviation CY vs LY (hidden)]),
   BLANK(),
   IF([Margin Deviation CY vs LY (hidden)>0,
      UNICHAR(9650),
      UNICHAR(9660)
     )
  )

Unichar is a great function which returns the Unicode character referenced by the numeric value, we can draw really cool stuff with this function.
(UNICHAR(9650) Up-Arrow , UNICHAR(9660) Down-Arrow)


Visualising everything together

Now let’s put together our measures into a matrix visual:

  • Margin %: Margin by employee and by year
  • All Margin: Yearly margin of the entire company
  • Margin Deviation: This is simply the difference between the employee margin and the average margin of the entire company
  • Margin Deviation CY vs LY: Current Year Margin Deviation versus Last Year Margin Deviation
Capture3

How do we read this result:

Andrew Ma:
In 2013 the entire company margin AVG was 46.9% and Andrew margin was 35.46% hence below the AVG margin by -11.46%.
In 2014 the AVG margin is 74.32% and Andrew has now performed better than the AVG margin by 8.42%.
Note that Andrew has also increased his margin Deviation by nearly 20% (8.42–11.44) so he got a up arrow.

Tina Lassila:
In 2013 Tina realised a Margin% of 101.44% and she beat the AVG margin by 54.54%.
In 2014 Tina realised a Margin% of 116.67% which is a nice increase compared to 2013!
However, it turns out that the whole company performed better in 2014…
So if we look at Tina’s Margin Deviation compared to the last year we notice that she did not perform as good as in 2013 ans thus got a down arrow.

A nicer visual

Capture2
Capture5

To get this visual we just need to keep the previous matrix above, remove the first two columns and then apply some conditional formatting using the Margin Deviation CY vs LY (hidden) measure.
And by looking at this visual we can clearly observe that Tina performance compare to the AVG Margin has decreased by around 10%.

In most cases the margin indicator is used to asses people, team or product performance.
However, by using the Margin only we’re missing out the overall trend of the company.  At first sight an increase of 15% seems great but once we realise that the overall increase was actually of 28% it doesn’t seem great anymore.

This is why margin deviation from average can provide really great insight from our data but using it alone could also be misleading as even if Tina Margin Deviation is not that good she’s still the best performer for the year of 2014.
So I think combining the Margin% and Margin Deviation from average measures together is always a good idea!

Here is the power BI Customer Profitability sample that I used for this article.

DAX Time Intelligent – Single Measure For Multiple Dates

Let’s assume a scenario where we have 50 time intelligent measures and 5 dimension Dates (order, invoice, ship, due, calendar…).
If we want the 50 measure to interact with the 5 dimensions we would need to create 250 measures which might rapidly overwhelm the users as they must sift though so many measures.

This script below allows you to drag any dates with the YTD margin measure and the measure will dynamically apply the calculation accordingly and thus keeps our number of measures to 50 instead of 250.

Total Margin YTD:=
var AllDate = COUNTROWS(all('Order Date'))
--you can use any date dimension as long as they have the same number of rows
--you can also create a separate measure for the count all to avoid
--recomputing and retyping the var calculation each time
return
switch(
TRUE(),
COUNTROWS('Order Date')&lt;AllDate,TOTALYTD([Internet Total Margin],&#039;Order Date&#039;[Order Date]) ,
COUNTROWS(&#039;Due Date&#039;)&lt;AllDate,TOTALYTD([Internet Total Margin],&#039;Due Date&#039;[Due Date]),
COUNTROWS(&#039;Ship Date&#039;)&lt;AllDate,TOTALYTD([Internet Total Margin],&#039;Ship Date&#039;[Ship Date]),
TOTALYTD([Internet Total Margin],&#039;Order Date&#039;[Order Date])
)   

Note that you can also use the function IsCrossFIltered(datekey) instead of CountRows() but you cannot use IsFiltered() or HasOneFiltered() as it returns TRUE only when a column is being filtered directly.
So far I seem to have just a little bit better performance with the countrows() version but I haven’t really tested it on a large database.

The main drawback of it is that we need to create many role-playing dimension instead of keeping only one dimension and taking advantage of the UseRelationship DAX function. But role-playing date dimensions are very small so memory consumption won’t be an issue anyway!

Here I use the same measure with three different dates. (AdventureWorks2016 sample data)
Capture

 

This technique might become useless once the new “calculation group”  feature of SSAS 2019 will be released.

 

SSRS Multi-Value parameter in DAX

Setting up Multi-Value Parameter using DAX in 3 steps

For those who are already familiar with SSRS using MDX it’ll be pretty straightforward as this is exactly the same logic, only the syntax changes…

You can download the  AdventureWorks Tabular Database Sample here

1. Multi-Value Parameter

We first need to create a parameter e.g “param_CountryRegionName” and then generate the country with he following DAX query:

capture

 EVALUATE SUMMARIZECOLUMNS('Geography'[Country Region Na
me])

We can run the above code in the DAX designer to see the multiple values of the CountryRegionName parameter

capture

2. Main Data Set

Once the parameter list is ready we need to create our main dataset.
We want to return the Internet total sales as well as the margin grouped by Product Category with the ability to filter by Country Region and by Fiscal Year.

EVALUATE
SUMMARIZECOLUMNS (
'Product Category'[Product Category Name],
FILTER (
VALUES ( 'Geography'[Country Region Name] ),
PATHCONTAINS ( @CountryRegionName, 'Geography'[Country Region Name] )
),
FILTER (
VALUES ('Date'[Fiscal Year] ),
PATHCONTAINS ( @FiscalYear, 'Date'[Fiscal Year] )
),
"Internet Total Sales", [Internet Total Sales],
"Internet Total Margin", [Internet Total Margin]
)

Here the trick is to use PATHCONTAINS; in this example PATHCONTAINS creates a string as a result of evaluating the parameter values and then checks for each country if they’re among the parameter values string.

3. Link the parameters to the main data set

capture

capture

 =join(Parameters!CountryRegionName.Value,"|") 

Here the second trick is to use the SSRS function “Join” to concatenate the list of the selected parameters with a pipe.

Note: You must concatenate your parameter values with a pipe “|” as the Pathcontains function expects a pipe delimited string. (Read more about the DAX PATHCONTAINS  and PATH functions)

capture

Export Data from Power BI into a file using R

We usually import Data from file into Power BI, but exporting data from Power BI can be very handy when you want to create a custom visual using R.

In fact it can be very cumbersome to code your visual directly into the Power BI script editor.
Here are few reasons why you should opt for exporting your Power Bi dataset first and re-import it in R to create your visual.

  • Intellisense is not available in Power BI R script embedded
  • Does not highlight keywords in colour
  • Hard to debug & hard to code (you can’t print intermediate calculation)
  • Slower than Rstudio

So unlike you’re a R master or you want to create a very simple visual it is definitely worth exporting your data into a file and then re-import it into R.

You can then create your visual in Rstudio first and once you’re happy with it just copy and paste your code into the Power BI visual script.

Export you data

If you haven’t already installed the package (gdata) you’ll need to install it:

#open an instance of R and type the command below
install.packages("gdata");

 

Once the “gdata” package is installed, select the R visual script and drag into values the measures and columns you need.

Capture

In the R script editor type the following R code:

require(gdata)
write.table(trim(dataset), file="your filepath.txt", sep = "\t", row.names = FALSE)

Capture

You can add plot(dataset) like I did int the above screenshot to make sure there isn’t any errors in your script hence as long as you can see a plot whatever it is(line-plot, box-plot, correlation-plot) it means your export was successful or obviously you can just check if your file is present in your directory.

Here is my output file:
Capture

Re-import you Power BI dataset into R

Now we can import our Power BI dataset into R as follows:

dataset = read.table(file="myfile2.txt", sep = "\t",header = TRUE)

See the R output below:
Capture

You can now work with your dataset in Rstudio until you get your visual right and then you’ll just need to copy & paste your code into the Power Bi script..

 

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 " &amp; [TopN Value] &amp; " 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.