Forum Discussion
Creating risk matrix on PowerBI
em101, I apologize for the delay on this. I have attached a .pbix for a possible way to implement the risk matrix. This file contains power query transformations, relationships, measures, and the matrix visual itself.
You'll definitely need to adjust things but hopefully this is enough to get you going. If so, please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.
Hi em101. See attached.
Here's a relevant link that may be helpful in the future for you related to sorting: Sort one column by another column in Power BIIf this is enough to get you going please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.
Hi giammariam, sorry to bother you again. In the Power BI file, I was just wondering in the 'Jira' table did you have to manually enter in the 'Likelihood Number' and 'Consequence Number' for each risk or were you able to set this up with some sort of formula/DAX? If you are able to provide any sort of summary of the steps you applied (particularly around doing the comp_key_Likelihood_Consequence and _issueKeyConCat) that would be great for my understanding. Thank you
Hi em101, sure. The 'Liklihood Number' and 'Consequence Number' were not manually added to the Jira query. Below are the steps that I implemented. The heavy lifting was done in Power Query. #4 is the critical piece.
1. I created 3 lookup queries using the "Enter Data" option: "Consequence", "Likelihood", "5x5 Unpivoted." My understanding is that these are static.
2. The "Jira" also used the "Enter Data" option, but in your implementation you'll want to use your source (CSV, Excel, etc.)
3. The [comp_key_Likelihood_Consequence] is from an abandoned implementation that I had tried earlier. My apologies for not removing it and creating confusion. You can disregard it.
4. In the Jira query, the Likelihood query is first merged into the Jira query on fields [Residual Likelihood] -> [Likelihood] and the [Likelihood Number] from the Likelihood query is added. Then Consequence query is merged into the Jira query on fields [Residual Consequence] -> [Consequence] and the [Consequence Number] from the Consequence query is added.
By adding the two fields in step 4 to the Jira query, we are able to create the following model in Power BI Desktop, and map from the Jira query to the 5x5 Unpivoted query through the Likelihood and Consequence lookup queries:
I hope this helps.
Hi giammariam
Thanks so much for your response.
For Step 4, I can see the "Merge Queries" option which I understand you use for merging fields [Residual Likelihood] -> [Likelihood], but what do you use to add the Likelihood Number query to the Jira query? Is it on the Append Queries dropdown or do you use something else?
Also can you please explain the steps on how you got the background and foreground colour measures to appear on the Matrix visual?
Many thanks for your help.