Como criar uma Matriz de Riscos 5x5 no Power BI (Iniciante)

Name: Como criar uma Matriz de Riscos 5x5 no Power BI (Iniciante)
Uploaded: 2023-03-19T19:22:12.000Z
Duration: 52 min 44 s

Creating a Risk Matrix in Excel and Power BI

In this video, Professor Anderson demonstrates how to create a risk matrix using Excel and Power BI. He explains the purpose of each sheet in the Excel file and how they are used to create the final risk matrix visualization in Power BI.

Setting up the Excel File

The first sheet is called "First Evaluation" and contains fields for evaluating risks, including event number, risk category, impact, probability, inherent risk, classification, and position.

The second sheet is a copy of the first sheet for subsequent evaluations.

Additional sheets can be created by duplicating the second sheet.

Values can be edited on subsequent sheets to reflect changes in risk evaluation.

Using the Position Column

The position column identifies which quadrant of the 5x5 matrix each risk belongs to.

A separate sheet called "Matrix" consolidates data from all previous sheets into a single table that counts occurrences of each position value.

Creating the Risk Matrix Visualization in Power BI

After creating an Excel file with multiple sheets containing data on evaluated risks, import it into Power BI.

Use filters to refine data displayed on dashboard.

Create a 5x5 matrix visualization using position values from consolidated "Matrix" table.

Add markers or X's to indicate where risks are located within matrix based on their position values.

Overall, this video provides a clear demonstration of how to use Excel and Power BI together to create a risk matrix visualization. The Excel file is set up with multiple sheets for evaluating risks, and the position column is used to identify where each risk belongs within the 5x5 matrix. Finally, Power BI is used to create a dashboard that displays the risk matrix visualization based on data from the Excel file.

Creating a Matrix in Excel and Power BI

In this section, the speaker explains how to create a matrix in Excel and Power BI.

Creating Positions for New Evaluation

Copying the formula from one sheet to another.

Creating 25 positions by dragging and changing color.

Importing data into Power BI.

Checking Relationships Between Sheets

Confirming that there are no blank columns or rows in the imported data.

Establishing relationships between sheets.

Creating Segmentation of Data

Adding a segmentation of data to select between three cycles of evaluation.

Formatting the visualization to allow only one selection at a time.

Creating Quadrants for Matrix

Adding cards for each quadrant of the matrix.

Creating a Matrix in Excel

In this section, the speaker demonstrates how to create a matrix in Excel using filters and advanced filtering techniques.

Using Filters to Create a Matrix

To create a matrix, open the filter tab and select the position column.

Drag the position column into the filter and change from advanced filtering to basic filtering.

Select the desired position for your matrix and copy and paste it for each quadrant.

Formatting the Matrix

Add borders to make it easier to follow along with the construction of your matrix.

Use colors to differentiate between different levels of risk (e.g. red for extreme risk, yellow for medium risk).

Use font color that matches background color to hide zeros in cells where there is no occurrence of risk.

Formatting Risk Levels in Excel

In this section, the speaker shows how to format different levels of risk in an Excel spreadsheet using colors.

Formatting Risk Levels

Use different colors (e.g. red, yellow, green) to represent different levels of risk (e.g. high, medium, low).

Name each quadrant according to its level of risk (e.g. extreme risk, high risk).

Copy formatting across all quadrants with similar levels of risks.

Finalizing Your Matrix

In this section, the speaker provides final touches on your matrix by adding labels and formatting text.

Adding Labels

Label each quadrant according to its level of risk (e.g. extreme risk, high risk).

Add labels for rows and columns as necessary.

Formatting Text

Choose an appropriate font size and style that is easy on eyes.

Format text so that it is visually appealing and easy-to-read.

Formatting the Risk Matrix

In this section, the speaker formats the risk matrix by adding colors to each quadrant and making sure that the numbers are invisible when they are zero.

Formatting Medium Risk Quadrant

Add yellow color code to medium risk quadrant.

Use formatting brush to apply same format to other quadrants.

Check formatting against TCU model and name fields.

Formatting High Risk Quadrant

Choose dark orange color for high risk quadrant.

Apply conditional formatting rule for non-zero values in high risk quadrant.

Name fields in high risk quadrant.

Formatting Extreme Risk Quadrant

Choose a darker shade of red for extreme risk quadrant.

Apply two conditional formatting rules: one for zero values and another for values greater than 100.

Copy formatting to other cells in extreme risk quadrant.

Name fields in extreme risk quadrant.

Risk Matrix

In this section, the speaker discusses the extreme risk 80.

Understanding Extreme Risk

The speaker introduces the concept of extreme risk 80.

Conclusion and Next Steps

In this section, the speaker concludes the video and suggests next steps for viewers who want to improve their dashboard.

Concluding Remarks

The speaker concludes the video by stating that they have completed their matrix and encourages viewers to experiment with their data.

If viewers need help with filters or improving their dashboard, they can watch a second video that will be released soon after this one.

Final Thoughts

The speaker signs off by wishing viewers good luck in their business endeavors and thanking them for watching.