2022 Week 10 | Power BI: Risk Matrix

Introduction

Your challenge this week is to create a Risk Matrix with some supporting visuals.  We decided to keep the project management theme going this week.  If you did not do last week’s challenge, I highly recommend you checkout Kerry’s Gantt Chart challenge, here.  

For this challenge, I looked at a lot of possible ways to create a Risk Matrix but none of them were as clean and simple as utilizing Synoptic Panel.  If you remember, I utilized this tool in week 4 to create a Hex Map.  This method of creating a Risk Matrix was just too good to not use it, plus you get to see how you can use custom visuals in Synoptic Panel.

The dataset we are using is an open dataset from with City of Phoenix where we have open access to claims made against the city.

In this dataset, there is a row for each claim but we will be analyzing the data at the cause level.  Below are the definitions for how the scores should be calculated.

  • Claim Risk by Cause
      • High (3) – More than 10 claims
      • Medium (2) – More than 2 claims
      • Low (1) – More than 0 claims
  • Cost Risk (note – these numbers are cost per claim)
      • High (3) – More than $2,000 per claim
      • Medium (2) – More than $500 per claim
      • Low (1) – More than $0 per claim

Requirements

BUILD THE DATA MODEL (OPTIONAL)

  1. Download the following files from data.world or from the City of Phoenix website.
  2. Append the Queries
  3. Filter for Claim Status = “F” and for 2021 claims
  4. Group by Causes to get Cost, Claims, and Cost Per Claim at Cause granularity
  5. Create a custom table (see original format below) 
      Claim Risk
      123
    Cost Risk1LowLowMedium
    2LowMediumHigh
    3MediumHighHigh
  6. Transform custom table into the following fields
      • Cost Risk
      • Claim Risk
      • Overall Risk (Low, Medium, High)
      • Risk Code (11,12,13,etc – Claim Risk & Cost Risk Concatenation)
  7. LEFT JOIN the aggregated cause table to the custom risk table
      • NOTE – Joining in this order provides null values for cost and claim risks that have no causes associated with them

 LET’S BUILD THE RISK MATRIX!!!

  1. If you prefer to start with a complete data model, please download the Power BI Desktop (.pbix) file.
  2. Download the .png document included in the data.world link, here.  You can also build your own if you are feeling ambitious.
  3. Go to synoptic.design and upload the .png file.
  4. Name each container according to a numeric code that can be joined with the data (HINT: it is a concatenation of the claim risk and the cost risk (11,12,13,21,etc))
  5. Export to PowerBI – Save the .SVG file
  6. Go to Power BI and Import the custom visual, “Synoptic Panel by OKViz”.
  7. Start by dragging in your numeric risk code in the category field and the distinct count of causes into the measure.
  8. Figure out how to create states from the states measure to assign a color coding to indicate the overall risk.  Make sure to include all relevant information in the tooltips.
      • HINT – Lookout for where the risk matrix has no valid causes associated with it (Distinct Count of Cause will show 1 instead of 0 since it is counting null)
  9. Build a bar chart and scatter plot to interact with the risk matrix
  10. Perform final formatting.  Match my format or put your own spin on it.

 GOOD LUCK!

Dataset

This week you can find the data here. You have the option to build the data model from scratch or start with the completed data model.

Share

After you finish your workout, share on Twitter using the hashtags #WOW2022 and #PowerBI, and tag @JSBaucke@MMarie, @shan_gsd, @KerryKolosko, and @NerdyWithData. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year. 

Solution

3 thoughts on “2022 Week 10 | Power BI: Risk Matrix”

  1. Hello, I really like this video and the visuals that you’ve created! I have tried to recreate this for a project I’m doing for work but we have a 5×5 matrix instead. So I slightly tweaked the image I created for the Synoptic Design image but other than that I’m essentially trying to do the same thing. However I cannot get the nulls to populate a “0” using your formula – Measure 2 = IF(ISBLANK(CALCULATE(DISTINCTCOUNT(‘Risk_Register'[Risk Code]),’Risk_Register'[Risk Code]BLANK())),0,CALCULATE(DISTINCTCOUNT(‘Risk_Register'[Risk Code]),’Risk_Register'[Risk Code]BLANK()))

    When I drop that measure into the “Measure” field it counts the correct quadrants that have data but the ones that don’t remain black. Also the count of each quadrant is fixed at 20 where that is not the case. I tried to make my own formula which is here – Measure = IF(ISERROR(COUNT(Risk_Register[Risk Code])),0,COUNT(Risk_Register[Risk Code]))

    This formula that I came up with puts the right count in the squares that have data but again it leaves the nulls as black.

    Any chance I could reach out to you to try to figure out where I’ve gone wrong?

    Thank you very much for the video regardless…

    1. Hey Kyle. I saw your message to me on LinkedIn. We can connect there and you can send me the file so I could have a look.

Leave a Reply to Rob Saunders Cancel Reply

Your email address will not be published. Required fields are marked *

Scroll to Top