How to Use Custom Power KPI Matrix in Power BI Desktop

By:   |   Updated: 2023-08-02   |   Comments   |   Related: > Power BI Charts


Microsoft Power BI Desktop provides users with various certified custom visuals. Sometimes one wants to highlight multiple key performance indicators, justifications, and supporting evidence; in that case, the Power KPI matrix provides all the support to fulfill the need. This visual has a wide variety of customizable options, making it easier to meet all the desired requirements. In this tip, we will use this custom visual and guide you through each step to maximize its usage.


For an organization to achieve its desired goals and implement an effective business strategy, measurement is an essential management tool, and a good senior management team understands their firm's measurement system is strongly required for them to be up to date with the overall business performance. Businesses must assess their overall standing to deploy timely strategies in areas that need improvement and satisfy their stakeholders when analyzing factors like profit margins and market share. Typically, business performance is evaluated using Key Performance Indicators (KPIs), essentially a set of quantifiable measurements designed to gauge the overall long-term performance of a company.

A KPI demonstrates how well a company is performing in a specific area by comparing the actual value obtained from the company's database with the desired target value set by management. This information can be condensed graphically, as shown below.

A typical KPI Visual

In the above illustrated KPI visual, we can see the following components:

  • Actual Value: The actual value is encoded by the green sales trend, reaching the value of '400'.
  • Target: The target set for the previous year's sales was 350.
  • KPI Index: This metric categorically encodes the relationship between the target and actual value. For example, if the actual value exceeds the target, the KPI index can be encoded as 1; otherwise, it can be set to 0.
  • Status Indicator: Building upon the previous example, if our KPI index for a particular month's sale is '1', we can use a green symbol to indicate that the KPI exceeds targets, as shown above.

Although the above visual captures only a single KPI, we can use a KPI matrix to display unlimited business metrics in a single tabular form. As we will see later, a KPI matrix visual can group KPIs according to their category and display the current actual value, comparison value, KPI status, and the trend for all the required metrics in a formatted tabular list.

One widespread application of a KPI matrix, which we will also demonstrate in this tip, is the Balanced Score Card (BSC). Devised by the Harvard Business Review, BSC is a strategic management performance metric that gives managers a fast and comprehensive view of the business. This measure is different as it complements financial and non-financial measures, giving a bigger picture of a business's standing. Often the traditional KPIs are solely focused on a business's financial metrics, which communicates an incomplete and false view of organizational performance.

Four perspectives of balanced scorecard

As illustrated above, BSC allows managers to look at the business from four critical perspectives:

  • Finance: How do we look to the shareholders? This includes traditional financial measures such as profitability, revenue growth, and return on investment.
  • Customer: How do customers see us? This perspective analyzes organizational performance from a customer's point of view. It includes measures such as customer satisfaction, customer retention, and customer acquisition.
  • Internal: What must we excel at? This perspective assesses what a company must do internally to meet customer and stakeholder expectations. Process efficiency, productivity, and cycle time that drive organizational performance are monitored.
  • Learning & Growth: Can we continue to improve and create value? This perspective emphasizes the organization's ability to learn, innovate, and develop its people and capabilities. Here, we can assess employee retention, training and development, and organizational culture.

Creating a Data Model in SQL Server

Now that we understand the fundamentals of KPIs and BSC, it is time for a more practical demonstration. We will create a schema using SQL Server to represent the yearly performance of a business entity in areas specific to dividends, net profit, customer engagement feedback score, active customer base, employee engagement feedback score, labor efficiency, and losses due to equipment failure. These metrics will later form our KPIs.

To start, we first need to create our database:

Now we will create individual tables for each KPI metric mentioned above. The tables will include a monthly date column, the actual value, and our target value for that specific area.
CREATE TABLE dividends
    date Date,
    actual_value int,
    target int
CREATE TABLE net_profit
    date Date,
    actual_value int,
    target int 
CREATE TABLE customer_engagement_feedback_score
    date Date,
    actual_value decimal(3, 1),
    target decimal(3, 1)
CREATE TABLE active_customer_base
    date Date,
    actual_value int,
    target int 
CREATE TABLE employee_engagement_feedback_score
   date Date,
    actual_value decimal(3, 1),
    target decimal(3, 1)
CREATE TABLE labor_efficiency
   date Date,
    actual_value int,
    target int 
CREATE TABLE losses_due_to_equipment_failure
    date Date,
    actual_value int,
    target int 

Now we can populate these tables.
('2022-01-31', 40, 55),
('2022-02-28', 60, 55),
('2022-03-31', 30, 55),
('2022-04-30', 40, 55),
('2022-05-31', 50, 55),
('2022-06-30', 30, 55),
('2022-07-31', 55, 55),
('2022-08-31', 54, 55),
('2022-09-30', 60, 55),
('2022-10-31', 70, 55),
('2022-11-30', 65, 55),
('2022-12-31', 60, 55);
('2022-01-31', 95000, 100000),
('2022-02-28', 110000, 100000),
('2022-03-31', 80000, 100000),
('2022-04-30', 90000, 100000),
('2022-05-31', 105000, 100000),
('2022-06-30', 100000, 100000),
('2022-07-31', 110000, 100000),
('2022-08-31', 102000, 100000),
('2022-09-30', 98000, 100000),
('2022-10-31', 109000, 100000),
('2022-11-30', 115000, 100000),
('2022-12-31', 105000, 100000);
INSERT INTO customer_engagement_feedback_score VALUES
('2022-01-31', 7.0, 8.0),
('2022-02-28', 7.6, 8.0),
('2022-03-31', 7.8, 8.0),
('2022-04-30', 7.6, 8.0),
('2022-05-31', 7.9, 8.0),
('2022-06-30', 8.0, 8.0),
('2022-07-31', 8.1, 8.0),
('2022-08-31', 7.9, 8.0),
('2022-09-30', 8.2, 8.0),
('2022-10-31', 8.3, 8.0),
('2022-11-30', 8.4, 8.0),
('2022-12-31', 8.5, 8.0);
INSERT INTO active_customer_base VALUES
('2022-01-31', 630, 600),
('2022-02-28', 620, 600),
('2022-03-31', 625, 600),
('2022-04-30', 635, 600),
('2022-05-31', 630, 600),
('2022-06-30', 635, 600),
('2022-07-31', 640, 600),
('2022-08-31', 645, 600),
('2022-09-30', 650, 600),
('2022-10-31', 645, 600),
('2022-11-30', 640, 600),
('2022-12-31', 635, 600);
INSERT INTO employee_engagement_feedback_score VALUES
('2022-01-31', 6.0, 7.0),
('2022-02-28', 5.9, 7.0),
('2022-03-31', 6.1, 7.0),
('2022-04-30', 6.2, 7.0),
('2022-05-31', 5.8, 7.0),
('2022-06-30', 5.5, 7.0),
('2022-07-31', 5.6, 7.0),
('2022-08-31', 5.3, 7.0),
('2022-09-30', 5.6, 7.0),
('2022-10-31', 5.2, 7.0),
('2022-11-30', 5.4, 7.0),
('2022-12-31', 5.7, 7.0);
INSERT INTO labor_efficiency VALUES
('2022-01-31', 60, 50),
('2022-02-28', 65, 50),
('2022-03-31', 60, 50),
('2022-04-30', 55, 50),
('2022-05-31', 64, 50),
('2022-06-30', 68, 50),
('2022-07-31', 74, 50),
('2022-08-31', 75, 50),
('2022-09-30', 78, 50),
('2022-10-31', 80, 50),
('2022-11-30', 84, 50),
('2022-12-31', 82, 50);
INSERT INTO losses_due_to_equipment_failure VALUES
('2022-01-31', 1400, 1000),
('2022-02-28', 1600, 1000),
('2022-03-31', 800, 1000),
('2022-04-30', 600, 1000),
('2022-05-31', 1800, 1000),
('2022-06-30', 340, 1000),
('2022-07-31', 400, 1000),
('2022-08-31', 1200, 1000),
('2022-09-30', 300, 1000),
('2022-10-31', 500, 1000),
('2022-11-30', 570, 1000),
('2022-12-31', 1900, 1000);

To view the created tables, use the 'SELECT' statement, as shown below.
SELECT * FROM kpi.dividends;
Dividends table
SELECT * FROM kpi.net_profit;
Net profit table
SELECT * FROM kpi.customer_engagement_feedback_score;
customer engagement feedback score table
SELECT * FROM kpi.active_customer_base;
Active customer base table
SELECT * FROM kpi.employee_engagement_feedback_score;
Employee engagement feedback score table
SELECT * FROM kpi.labor_efficiency;
Labor efficiency table
SELECT * FROM kpi.losses_due_to_equipment_failure;
Losses due to equipment failure table

Now that we are done with our individual KPI tables, we must create a table that compiles all the KPIs. This will be useful later when developing our visualization in Power BI. This table will consist of a column of the relevant KPI type and its corresponding BCS category, alongside a threshold status for that KPI.
CREATE TABLE KPI_definitions
    KPI_id int,
    KPI_type text,
    threshold text,
    category_id int,
    BSC_category text
('1', 'dividends', 'Above Is Good', 1, 'Finance'),
('2', 'net_profit', 'Above Is Good', 1, 'Finance'),
('3', 'customer_engagement_feedback_score', 'Above Is Good', 2, 'Customer'),
('4', 'active_customer_base', 'Above Is Good', 2, 'Customer'),
('5', 'employee_engagement_feedback_score', 'Above Is Good', 3, 'Learning & Growth'),
('6', 'labor_efficiency', 'Above Is Good', 4, 'Internal'),
('7', 'losses due to equipment failure', 'Below Is Good', 4, 'Internal');

We can view our table below.
SELECT * FROM kpi.kpi_definitions;
KPI definition table

Creating a Visualization in Power BI

With a rudimentary data model, we can import it into Power BI and create BSC using the Power KPI matrix.

Step 1

In the main interface of Power BI desktop, click "Get Data" in the "Home" ribbon and select the "SQL Server" option, as shown below. Power BI also offers a range of other sources from which you can import your dataset.

Common data sources in Power BI

The "SQL Server database" window will open. Enter the relevant server and database credentials and click "OK," as shown.

SQL Server database window

Step 2

If the connection with the database has been successful, the "Navigator" window will pop up. Select all the tables and click "Transform Data." Power BI also allows us to preview our tables, as seen below.

Navigator window

Step 3

The "Power Query Editor" will open. Our goal is to consolidate the individual KPI tables previously created. Before we can append them, we need to introduce another column in our tables that mentions the KPI name for that table for each observation. This step will differentiate each row according to its relevant KPI in the final appended table.

Select any KPI table under the "Queries" tab, as shown below. Let's start with the "dividends" table.

Power query editor window

In the "Add Column" ribbon, select the "Custom Column" option, as shown below.

Custom column creation

The "Custom Column" window will open. Enter the new column name and the KPI name in the syntax box, as shown below. Click "OK."

Custom column window

This will take you back to the "Power Query Editor," where we can observe our newly appended column. To assign it a valid data type, in the "Home" ribbon, click the "Data Type" box shown below to the far right, and then select the "Text" option.

Changing data type of new column

Repeat this step for all remaining KPI tables (excluding the KPI_definitions) to get the tables shown below:

New KPI_type column for dividends table
New KPI_type column for net profit table
New KPI_type column for employee engagement feedback score table
New KPI_type column for active customer base table
New KPI_type column for employee engagement feedback score table
New KPI_type column for labor efficiency table
New KPI_type column for losses due to equipment failure table

Step 4

Now we are ready to append all our KPI tables into one table. In the "Home" ribbon, click "Append Queries" and then "Append Queries as New," as shown below.

Process for appending tables

The "Append" window will open. Select the "Three or more tables" option, as shown below, click on the KPI table names one by one, and click the "Add >>" option. Click "OK."

Append window

We will now be able to observe our new table. In the "Properties" tab, change the table's name and ensure all the data types of each column are correct.

New appended table

Step 5

Now that we are done with the data manipulation, we can exit the query editor. In the "File" ribbon, click "Close & Apply" as shown.

Closing power query editor

Step 6

Once redirected to the main interface, click the "Model view" tab, as shown below. We need to ensure that the relationships in our data models are correct.

Main interface of Power BI

Our current data model will be illustrated below. As we now have the "Score Card KPI," which appends all of the individual KPI tables, we don't need the redundant connections between the "KPI_definitions" table and the individual KPI tables from before.

Initial data model

Thus, we will be deleting those connections. Click on the connection line and select the "Delete" option. Repeat for all the redundant connections.

Deleting redundant connections between tables

Our final data model should look something like this:

Final data model

Step 7

Now we can revert to the main interface and create our KPI matrix visualization. In the "Visualization" tab, click "Get more visuals," as Power BI does not natively offer the visual we need.

Visualization and Data panel

The "Power BI Visuals" window will open. In the search box, type "KPI matrix" and install the custom visual extension shown below.

Power BI Visual window

Note: This visual is developed by Power BI; therefore, it is safe to use.

Power KPI Matrix package installation

Step 8

Select the Power KPI Matrix icon, as shown below.

Selecting Power KPI matrix visual

Before populating our visual, we must create a column representing the KPI index (discrete integer quantity to determine whether KPI is 'good'). In the "Data" field, as shown below, right-click the "Score Card KPIs" table and click "New column."

Creating new column

Insert the following DAX code in the given formula box. Using a 'SWITCH' statement, provide the KPI index a value of 1 if actual value is equal to or above the target or 2 otherwise.

Formula for the new column

Now, from the "Score Card KPIs" table, drag the "date" column to the "Date" field and "actual_value" to the "Actual Value" field, as shown below.

Populating the visual

Our "target" column goes to the "Comparison Value" field, and the newly created "KPI Indicator Index" measure goes to the similarly named field as shown.

Populating the visual

To display all the KPIs and their trends in the visual, we must put the "KPI_type" column in the "Row-based Metric Name" box as shown. To group these KPIs by their BSC category, we first put the "BSC_category" and then the "KPI_type" column in the "Category" field.

Populating the visual

Lastly, we can also sort our KPIs in the visual using our KPI and category IDs, as shown below.

Populating the visual

We now have a prototype of our KPI matrix. We can observe the four BSC perspectives and relevant KPIs in that category alongside their overall trends and compare the latest current and actual value of that KPI.

Initial Power KPI Matrix visual


Our visual, however, remains incomplete as there is a lot of formatting that we need to implement before we can analyze the business performance. Power BI allows formatting in two different streams, as shown below.

Formatting visual option

Visual Formatting

This option lets us alter our visual's appearance and some underlying mechanics. As we see below, these custom visuals have numerous choices we can change. We will be discussing a select few of them.

Visual Formatting options

Header. This allows us to alter the appearance of our column heading. We can change the background color, the text's font, size, style, and alignment to make the visual more appealing.

Header formatting settings

Category. Similar to the function of the "Header" setting described above, we can do the same for all of the observations present below a specific column, which is the "Category" column in this case.

Category formatting settings

As of Date. Since the visual compares the latest data points in the matrix, and all of our data ends at the end of December, we can disable our date column.

As of date formatting settings

KPI Indicator. For some of our KPIs, it wasn't always ideal for the actual value to exceed the target value. For instance, for losses due to equipment failure KPI, having the actual data below the target will be preferred. There we can change the KPI Indicator symbol accordingly here.

KPI Indicator formatting settings

General Formatting

This setting allows us to change the standard features to all visuals, as shown below. Here we can change the title and the size of our visual.

General formatting settings

We now have a finalized version of our BSC using the Power KPI matrix.

Final version of Power KPI matrix visual

This is an interactive visual. We can click on any trend and analyze it in more detail, as shown below.

Line graph pop ups

Since the visual compares the latest data in the matrix, we can interact and click on any required part of the line graph to get the percentage change relative to the target and the corresponding KPI indicator, as shown below.

Analyzing individual line graphs

Conduct Analysis

When it comes to evaluating this business's standing with the information we have, we can observe that the trends are generally improving for the financial and customer metrics, as well as labor efficiency. However, the company can improve its profit margins if it caters to above-target losses due to equipment failure. Employee satisfaction is also an alarming factor.

In this tip, we have discussed the basics and need for KPIs. We further developed the concept by using Power KPI Matrix to construct a balanced scorecard report.

Next Steps

Learn more about Power BI in this 3 hour training course.

Click here to start the Power BI course

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Harris Amjad Harris Amjad is a BI Artist, developing complete data-driven operating systems from ETL to Data Visualization.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-08-02

Comments For This Article

get free sql tips
agree to terms