How to create 9 Box Performance Grid Visuals in Power BI

A 9 Box Grid is a matrix that is used to evaluate and plot two different metrics in one view. This visual is mostly used by HR to evaluate employees’ performances and potentials.

9 Box: Performance Grid

In this post, I’ll try to explain how to create 9 Box Grid Analyses in Power BI.

We will have two pages. One will have employee count for each group and a scatter plot to see the distribution. Scatter Plot will have a tooltip to show employee’s box.

9 Box: Employee Count Box and Scatter Plot

Other page will have employee names for each box.

9 Box: Employee list per Box

Here is the randomized data that I use for this example. I’ve used alphabet as employee names. Calculated Potential with RAND() and Performance with RANDBETWEEN(1,12)

Example Data

As a first step, calculations for each box should be defined.

Calculation definition for each Box

Then, a measure should be created for grouping the employees. Since this will also help coloring, I named it as ColourCode. I use SWITCH() function to prevent nesting IF() statements.

9 Box: Measure for Groups

Also, I have created measures of employee count for each box. I use 9Box_n format for naming this measures. (n: number of the box)

9 Box: Employee count for box group 1

I’ll start with Employee Count Box visual. To create this box layout:

1- Add Card visual to the report

2- Add one of employee count measure(9Box_5)

3- Close Category in the format pane.

4- Add Background color accordingly

5- Reformat size

6- Repeat for each box

7- Add Text Box visuals to show Low, Medium and High labels.

8- Arrange layout.

9 Box: Employee Count Box

Then, I will add Scatter Plot. I’ll use default scatter chart for this.

1- Add scatter chart to the report

2- Add Employee names(person) to details field and add Performance and Potential to X Axis and Y Axis Fields

9 Box: Scatter Plot

3- For colors, change default data colors with conditional one. Format by Rules, based on ColourCode field. If this field is 1,2 or 3 color will be green(#00CC00); 4, 5 or 6 will be shown with yellow(#FFFF00); 7, 8 or 9 will be shown with red(#FF0000)

9 Box: Coloring Rule for scatter Plot

4- For the tooltip of scatter plot, a new page should be created. In page format pane, Tooltip option under page information should be open. I also customize the page size as 90x90 pixels.

9 Box: Tooltip Page Format

5- In this new page, there should be 9 Card visuals and Each of them need to has ColourCode as field. I aligned 9 30x30 pixel box within the page

9 Box: Boxes in the tooltip

6- For each box, data label color should be formatted by ColourCode Rule. First Rule will make the text white to match the background, and second one will override the first rule if given value is filtered. Below example is for the box number 5.

9 Box: Card Data Color Rule in tooltip- Box no:5

7- And for background color, color rule may only contain desired value.

9 Box: Card Background Rule in tooltip- Box no:5

At this point, tooltip page should look like as if it is blank.

8- In scatter chart format pane, open tooltip, select Type: Report page, and Page: Name of tooltip page that we created, 9box_tooltip.

That way, when we hoover over a dot in the scatter chart, tooltip will show which box this dot is in.

9 Box: Tooltip in Scatter Plot

To show employee names with in the boxes, Multi- Row Card should be used.

1- In new page, add Multi- Row Card with employee names(person) as fields.

2- Select Background colors for each box and add transparency for better label reading.

3- Add ColorCode filter to each box from filter pane to show only related employees.

9 Box: Employee Filter for box no:1

4- Arrange layout. I use 150x200 pixel card for each.

9 Box: Employee name list per box

That’s all!

I would like to hear about approaches that you will take if you want to create a 9- Box Performance Grid. What are the visuals that you would like to see in this kind of report/dashboard?