Transforming Risk Reporting: Build an Accessible Matrix in Power BI (Part 1)

In my experience developing reporting solutions for PMOs and Project Controls, the Risk Matrix is an absolute must-have. It’s a brilliant visual tool that helps us assess and prioritise potential risks in a project.

When I first designed a Risk Matrix in Power BI, I used a custom visual. The Synoptic Panel by OKViz is a versatile custom visual that lets us map non-geographic layouts, like risk matrices, floor plans, and process flows. This gave me the flexibility to fully customise the design, ensuring each cell was colour-coded and interactive, providing a user-friendly risk assessment tool. However, as we all know, custom visuals come with limitations, and many organisations restrict their use. This led me to explore a native Power BI solution using the built-in Matrix visual.

This is Part 1 of a two-part guide. In this post, I’ll take you through the step-by-step process, focusing on the data model and using M language (Power Query) to create the dimension table we will need to build the matrix. In Part 2, we’ll shift our focus to formatting the Risk Matrix for accessibility, ensuring clear contrast, text-based indicators, and a user-friendly layout.

To make it even easier to follow, I’ve also created a YouTube tutorial where you can see the process in action. So make sure you subscribe to get notified.

Building the Risk Matrix: A Fresh Approach

To ensure the Risk Matrix functions effectively, we must structure the data correctly. The key components are:

• A Facts Table containing your actual risk data (e.g. Risk Register).
• A Dimension Table containing the risk index, created by concatenating all possible combinations of Likelihood and Severity values.
• A Risk Index Key that connects our Facts Table with the Dimension Table.
• A Measure to Count Risks so we can visualise risk distribution.

The first challenge was creating the Dimension Table, listing Likelihood, Severity and the Risk Index, a unique code for each combination of Likelihood and Severity. We could potentially create these values manually in Excel or even input them directly into Power BI, but I decided to use Power Query (PQ) for that. Here is my approach.

The first step is to create a new blank query, where we list all possible combinations between Severity and Likelihood. This combination represents the position of the risk on the risk matrix. To achieve this in PQ, we’ll use the M coding language. We start by creating two lists, each containing whole numbers from 1 to 5. Then, we convert these lists into tables and combine them. It’s crucial to note that Severity should be the digit on the left, and Likelihood should be the digit on the right.

This is the M code that can be used to generate the Risk Index:

Once the Risk Index is built, we need to add 2 custom columns that will be used when building the Matrix visual. Using conditional logic, we can assign meaningful names to Likelihood and Severity values.

For the Row Labels, we will use the Likelihood. Using the right digit of the Matrix Index, we can create the following custom column with a conditional statement:

Where:

• Text.From([Risk Index]): This converts the [Risk Index] value to text.
• Text.End(text, 1): This extracts the last character of the text string.
• Using the Conditional Logic: The if…then…else statements evaluate the last character of the [Risk Index] text.

And the conditional format assigns the following labels:

• If the last character of [Risk Index] is “1”, it returns “1 – Rare”.
• If the last character of [Risk Index] is “2”, it returns “2 – Unlikely”.
• If the last character of [Risk Index] is “3”, it returns “3 – Possible”.
• If the last character of [Risk Index] is “4”, it returns “4 – Likely”.
• If the last character of [Risk Index] is “5”, it returns “5 – Very Likely”.
• If the last character does not match any of these, the code returns null.

Similarly, for the matrix Column Labels, we need to create a custom column, but this time using the Severity, the left digit of the Risk Index:

Where:

• Text.From([Risk Index]): This converts the [Risk Index] value to text.
• Text.Start(text, 1): This function extracts the first character of the text string.
• Conditional Logic: The if…then…else statements evaluate the first character of the [Risk Index] text.

And the conditional format assigns the following labels:

• If the first character of [Risk Index] is “1”, it returns “1 – Negligible”.
• If the first character of [Risk Index] is “2”, it returns “2 – Low”.
• If the first character of [Risk Index] is “3”, it returns “3 – Moderate”.
• If the first character of [Risk Index] is “4”, it returns “4 – Significant”.
• If the first character of [Risk Index] is “5”, it returns “5 – Catastrophic”.
• If the first character does not match any of these, the code returns null.

Bringing It All Together

With the Dimension Table ready, the next step is to create a matching Risk Index in the Facts Table and establish relationships in Power BI. To achieve this, we need to add a custom column to the Facts Table in PQ that combines the Severity and Likelihood scores, ensuring that Severity is on the left and Likelihood is on the right. Once this is done, we can create the relationship between the Facts and Dimension Tables.

To display risk distribution, we need to create an explicit measure to count the number of risks listed in the Facts Table:

_Count Risks = COUNTROWS(‘Facts Risk’) + 0

This approach ensures every Risk Index value is counted correctly, even when no risks are present (hence the + 0 to prevent blank results). This will be important when we format the background colour of the matrix.

Now we can build our visual! Here’s how:

• Rows: Add the field ‘Row – Likelihood’ from the Dimension Table.
• Columns: Add the field ‘Column – Severity’ from the Dimension Table.
• Values: Add the measure with the count of risks.

This is how the matrix will look. However, it’s not ready yet! We need to format it, including adding background colour. Traditionally, the Risk Matrix uses a Red-Amber-Green (RAG) colour scheme: green for low risk, amber for moderate, and red for high risk. But here’s the problem: RAG colours alone are not accessible.

Next week I’ll be posting Part-2 and a YouTube tutorial. Stay tuned!

Thank you for joining me on this journey. Until next time, let’s keep crafting accessible insights that make a difference!