All posts by Zomalex

Visualising A Daily Operational Process

The trading at a bank is managed at book level and there are thousands of books.  During the business day, each book moves through several different states starting with ‘Open’, then progressing through ‘Data OK’, ‘Calcs OK’, ‘Approved’, ‘Locked’ and finally ‘Closed’. This process is subject to strict deadlines – and it is important to monitor the overall progress during the day.

The visuals below show two ways of doing this. The bar chart shows the overall progress at a point in time – and it is more useful when it is extended into a side-by-side bar chart that can compare against the previous day or a historic average, so we can get an indication how well the current day is progressing.Power BI Desk Status SnapshotThe stacked area chart shows the progress through the day. The visual below shows at the end of the day – not all books have completed their journey to Closed status which could be a concern.

Power BI Desk Status Profile

I’ve simulated in Excel the source data for 100 books with snapshots of the status of each book taken at 30 minute intervals The main table has each book on a row and each column represents a half hourly timestamp.

Excel Status History

The book status is represented as an integer from 0 (Open) to 5 (Closed) and this mapping is in a separate table.

Excel Status List

The steps to make the charts are as follows

  • Import both the Excel sheets into Query Editor
  • Unpivot the data to obtain a row for each book at each timestamp
  • Load the data. The data model automatically joins the two tables on Status Id
  • In the data model, sort the Status Name column by Status Id so that our visualisations order the Status in the proper order rather than alphabetically
  • Build the visualisations

I have made a  5 minute YouTube video where I demonstrate building these charts

The Power BI visual is here.

If you would like to recreate it the sample source data is here.

First Look at Power BI Parameters

The Power BI Desktop April update contains several improvements, including templates, conditional columns and formatting tables but for me the most important are parameters since they make possible useful ways of interacting with Power BI reports.

For example, risk managers like to compare the VaR, a measure of the risk of a portfolios between any two dates they choose. Let’s call these two dates the “Start Date” and the “End Date”.  This is not possible by filtering the data – if we first filter all data by the Start Date, we have already filtered out the data for the End Date.

However, we can achieve this with parameters in the three easy steps. Firstly In the Query Editor, set up parameter Start Date with Date data type.

Manage Parameters Dialog

Create an ‘End Date’ parameter also.

Secondly create a new measure “VaR At Start Date” with the DAX below.

VaR At Start Date measure

Note that it references the “Start Date” Parameter. The MAX() is necessary since DAX has to be sure it is a single value. Of course our parameter contains only one value so we can safely do that.  Create a ‘VaR at End Date’ measure in the same way.

Finally add those measures as values well to a side-by-side bar chart and use another dimension on the ‘Axis’ well.

VaR Comparison Chart

The icing on the cake is to create the parametrised title for the chart by creating a DAX measure below that includes the Start Date and End Date parameters.

Comparison Title Measure

Chris Webb shows an advanced use of this technique in his blog.

Now users can change the parameters and the values on the chart refresh accordingly.

Enter Parameters Dialog

This first version of the parameters is very useful but there are clear ways that future versions may improve on this.

  • An obvious improvement would be to have a calendar picker for dates.
  • Also, at the moment we can only restrict values of our parameter to a list of allowed values – not helpful if your data contains 2 years of dates. It would be useful to set a range.
  • Furthermore we’d like to set a range of one parameter based on the value chosen by the user of another parameter – so that our End Date can never be earlier than our Start Date for instance.
  • When the user changes parameters, PBI desktop refreshes the data source. This is problematic if the data refresh takes more than few seconds.
  • In our example, changing the parameters does not actually require a data refresh.
  • Finally, parameters are available in the desktop but not yet in the service.

Having said all that, parameters do represent a big step forward in improving the usability of Power BI.


Using The R Script Visual in Power BI To Build Charts With Precise Layouts

Financial regulators are unreasonable people – at least that’s the impression those of us who report a bank’s risk numbers to them would have you believe. Not only do they demand daily reporting but they also require the charts that we provide to have a defined layout and format. This does raise a problem for Power BI in that while it has very good visual practices, these are not exactly those of the regulator. However, we can use the R visual to create a chart in exactly the fashion that a regular demands.

One very important chart for regulators is the market risk back test chart. It is realy two charts in one; a bar and area chart. It compares the profit and loss (P&L) that the bank, or part of the back such as a legal entity or desk, against a margin of safety, known as value-at-risk or VaR, that we promise that the P&L will stay within most of the time. On those days that the P&L exceeds the VaR, that’s a breach. If we have too many breaches, the regulator will take action.

The regulator wants to see the VaR as an area chart with VaR as a mirror image both above and below the x (date) axis since a breach occurs if the bank makes either a profit or a loss outside this VaR envelope. Regulators want this VaR envelope to be partly transparent so that we can superimpose the P&L on it. The daily P&L must be shown as blue bars with breaches shown in red.

In Power BI chart we can get close to these requirements – close, but no cigar.

Backtest built in native PBI

There are three problems

    • The VaR envelope is shown as two boundary lines rather than area chart. The Power BI combo chart visual combines a bar chart (used for the P&L) with a line chart but we can’t combine with any other chart
      • The P&L bars are all blue; there is no means of changing the colour of individual bars based on a condition
      •  The snapshot below show synchronized axes for P&L and VaR but if the the ranges of these two measures are sufficient different, the chart will automatically have different scales for P&L and VaR, which is not what we want and is potentially misleading.

However, we can meet all requirements if we use the R visual. We can use the fine tuning allowed in the plotting package to define our chart elements precisely. The snapshot below shows the R chart.

Backtest built in PBI R Script Visual

The R visual creates a script editor in a lower pane. It provides the first 2 lines of R code to create an R dataframe (R’s equivalent of a table) that contains all unique values of the columns in the values well. From here we can add a few lines of R code firstly to determine if there is a breach on a given day and then to plot the chart. The R code uses two very popular R packages. The dplyr package enables us to manipulate dataframes and the ggplot2 package gives us the ability to plot exactly the chart we require. The ggplot call first creates the P&L bar chart. It selects the colour of the P&L bars based on the breach condition. It then overlays this with partially transparent area charts – one for the upper VaR bound, then one for the lower (negative) VaR bound.

PBI R Script Editor Window

Even with a small amount of R code like this, it is best to build and test the R code within the RStudio, the typical development environment for R, then copy it into the code window for the visual once it is fully working.

The ggplot2 package allows fine tuning of a charts appearance – enough to satisfy the most unreasonable regulator.

This article first appeared on the Microsoft MVP Technical Tuesdays blog  here
The source data is here.

Time-Series Slicers In Power BI

In finance, we often need to look at a time-series over a large time-period, say a few years, then focus in on a much smaller range. Of course we can do this in Power BI desktop by putting the date (or month, quarter or year) in the filters area.

However, there are three custom visuals that we can use for convenient on canvas filters. The timeline, from Microsoft, shows a bar with a left stop (earliest date) and a right stop (latest date). The bar is divided into periods. There is a small slider that allows us to switch these periods between years, quarters, months, weeks and days. This works pretty well. I would like to see a few improvements however, to be able to disable certain periods. For example, when looking at a 5 year range, years and quarters are appropriate but, anything less than that is not a helpful.

The time brush, also from Microsoft and in experimental state, is even more convenient. It has a drag and drop feel; simply mouse-down at the start of the range and mouse-up at the end. The range on the slider is shaded. The time brush does have a couple of gremlins – for example, it shows a bar chart supposedly of the frequency of the observations but in that case I would expect a uniform distribution over time with my test data and it is clearly not showing that.

The advanced time slicer, from Cambridge Technology Partners, is an all-in-one visual, with a lower smaller bar chart showing the whole time range. It has drag selection like the time brush above and the larger chart above shows the selected period. Again there are some suggestions this is not ready for prime-time. The larger chart seems only to show the positive values and it calls itself the BrushChart in the visuals palette.

All three slider are shown in the example below. I will update this blog as they improve – all will become really useful slicers.

Creating histograms in Power BI

There are 2 ways to plot a histogram in Power BI – either use the custom histogram visualisation or bin the data beforehand and use a regular bar chart. Both have their advantages. I’ll create these to show the age profile of passengers on the Titanic using the popular dataset here

Here is the histogram.

The histogram is simple and quick to build and you can choose the bin width – or rather you can choose the number of bins and therefore the bin width indirectly once you know the range of the data. For example, the ages in this dataset range from 0 to 80 years – by choosing 16 bins, we get a bin width of 5 years.

We can see immediately that passengers in their twenties were the most popular group with corresponding fewer older passengers. There were also a large number of very young children.

To use the bar chart, we must first create a calculated column in the Query Editor for the bins. Let’s call it AgeBand and use a DAX formula like Number.IntegerDivide([Age], 5) to calculate it.
Add Custom Column - AgeBand
The drawback of this method is that it is slightly more complicated – we have to create this calculated column. If we want to change the bin width we must change the formula and re-load the data . However, the advantage of using a bar chart is that we can then put another category, such as Passenger Class on the legend to expand our analysis.

We can now see that older people were more likely to be in first class.

Visualising Risk

Risk Managers need to analyse and make decisions on data that changes at least daily. Each day trading and risk systems generate huge volume of data – millions, perhaps billions of rows. There is lot a variety within this deluge of data including trades, positions, sensitivities, prices and risk results such as value-at-risk (VaR). Reference data such as bank’s hierarchy, counterparty details, instrument details, countries, currencies are also required.

Dashboards and visualisations are essential to make sense of this. A good visualisation will provide a top-level view to bring out the big picture and overall trends, and highlight any outliers or exceptions that need attention. The best visualisations will then allow risk managers to drill down to the detail.

The next series of blogs will describe useful visualisations for showing certain aspects of risk and explain what features make them helpful. We will look at
• Tree maps for visualising VaR
• Mechanisms to drill down a firm’s hierarchy
• Bullet charts for comparing risk usage against limits
• Line charts to show P&L history and other time series

Links for ‘A Month Of Predictive Analytics’

I gave a short talk titled ‘A Month Of Predictive Analytics’ to the first meeting of the wonderful Meetup Mashup group on 18th June 2015. Here are the resources mentioned in the talk.

My slide deck is here.

Recent News about AI Machine Learning




  • Data Smart – John W Foreman
  • Practical Data Science With R – Nina Zumel and John Mount
  • Data Science For Business – Foster Provost and Tom Fawcett
  • Applied Predictive Analytics – Dean Abbot

Online Courses

My webinar for PASS Business Analytics Virtual Chapter (BAVC) comparing tools for exploratory data analysis is here together with previous webinars presented at PASS BAVC.

SQL Saturday Conference at Edinburgh ‘Learning R’ – Resources

Here are the links from my ‘Learning R through a typical BI task’ talk at the SQL Saturday Conference in Edinburgh on June 13th 2015.

The OneDrive folder contains

  • the slide deck
  • the R source code file for the Titanic demo
  • the CSV file containing the Titanic passenger list data used in the code above
  • the R source code file for the Iris demo

The books I mentioned in the Resources slide are

  • Data Smart – John W Foreman
  • Practical Data Science With R – Nina Zumel and John Mount

The course I mentioned in the Resources slide are

A presentation on Azure ML from Microsoft’s Andrew Fryer and Amy Nicholson featuring a sample to predict flight delays is on Amy’s One Drive

If you have any questions about the slide deck or the demo resources, please get in touch.