Walkthrough

I have created a walkthrough video of how to use SUMMARIZECOLUMNS in DAX to create a multilevel Sankey Chart in Power BI. Here is a link to the video, and I will describe some of the steps and calculations performed in the following paragraphs

Background

Quite a lot of the content surrounding Power BI typically involves some kind of transactional data recording sales, costs, profits, etc. I get that BI stands for Business Intelligence, therefore most of the content will be centred around how a business performs.

However, there are many other avenues that Power BI can be utilised for. I have worked in the Engineering, Construction and Legal fields, and have seen many potential applications that I am hoping to explore. I am looking at how I can possibly apply samples of datasets that I typically work with to create interesting analysis and visuals.

Risk Analysis and Sankey Charts

One such possibility is using data from Risk Analysis and Assessments and applying that to a custom visual like a Sankey Chart. Risk is often assessed by considering how likely a risk is to occur (Likelihood) versus it’s potential Consequence. Below is a typical matrix you may find that is used in many industries, including Construction.

Typically you will perform an assessment of the “Raw” risk; that is a risk that you have not yet taken any steps to reduce or “mitigate”. You will then have a Raw Risk score that can be classified according the above matrix.

Next, you would consider steps or controls that could reduce that risk, and effectively mitigate it. Once that has been documented, you can relook at the risk likelihood and consequence score, and reclassify it accordingly.

As such, you will create to scores for a risk:

• Raw Risk Score
• Mitigated Risk Score

These can be further classified into their colour rating:

• Red – Extreme
• Yellow – High
• Blue – Medium
• Green – Low

Having these multiple scores, and discrete classifications makes this kind of data ideal for a Sankey Chart , like that below. You move from an initial Raw Risk, to a Mitigated Risk, and then potentially on to some resulting action.

Data

I created a data template listing 40 Risks, with generic numbering, descriptions and scores. Below is a link to the file.

I then created two matrices in table form to mimic the scores of the above matrix

As per the video, I added a few steps in Power Query to combine the Likelihood and Consequence scores for the Raw and Mitigated Risks (using a “R” and “M” prefix, respectively). This is to differentiate between the two risk scores and allow the Sankey to interpret both as distinct.

After this, I created the model links in Power BI as shown below. This will allow us to create a summarized table using SUMMARIZECOLUMNS to bring in columns from differing tables.

SUMMARIZECOLUMNS

As the Sankey uses only three sets of data (Source, Destination and Weight), we want to recreate a summary table with only that data we want to use. For the Level 1 Sankey, we want to call up:

• Raw Risk (Source) – from Raw Risk Matrix
• Mitigated Risk (Destination) – from Mitigated Risk Matrix
• No. of Risks (Weighting) – from Risk Assessment Register

We can achieve this by using the following DAX code:

SUMMARIZECOLUMNS(
    Raw_Risk[R-Rating],
    Mitigated_Risk[M-Rating],
    "Number of Risks", [No. of Risks]
)

This gives us the following summary table

This will give a single level for the Sankey, but if we want to add another level, we need to effectively UNION another date group directly below this. Therefore, we use the same code and duplicate it using a UNION, like the below

Union(
    SUMMARIZECOLUMNS(
    Raw_Risk[R-Rating],
    Mitigated_Risk[M-Rating],
    "Number of Risks", [No. of Risks]
),
SUMMARIZECOLUMNS(
    Mitigated_Risk[M-Rating],
    Mitigated_Risk[Risk Action],
    "Number of Risks", [No. of Risks]
))

This then adds three rows at the bottom, to give us our second level in the Sankey

Building the Sankey

We can now drop the above three column titles into the Source, Destination and Weighting boxes for the Sankey to create it. There is a little manual adjusting required through drag and drop directly on the chart, as you will see in the video. Once you readjust and apply the colour codes you wish through formatting, you can get the below desired affect.

Next Level

If you wish, you can add a few additional steps to take the visual to the next level and create something that could be used in real life (with a little more tidy up, editing and adding to an overall dash). You could add cards to calculate the total number of mitigated risks by level, and maybe add some links to some separate pages where you could have drill down detail, like that shown below.

Card Code

The DAX code for those cards is shown below. It can be recreated for each level by simply replacing the Rating text.

Thanks

Thanks for reading, if you liked this example of using non-commerce type data in Power BI, let me know either in the video or comments below, or on LinkedIn. If there are other types of data or visualizations you would be interested in seeing, let me know.

Gerard – DG Analysis.