Use Case

How to Build a KPI Dashboard with Power BI

Written by Andy McDonald
Reviewed by Johannes Åkesson
May 21, 2024
10 min read

In this article, we’re going to show you how to build a KPI dashboard in Power BI for managers. The example we’ll work through uses our KPI data collection template, used by operational level administrators to collect KPI metrics. This template was built with SQL Server, Excel and the SQL Spreads Excel Add which is discussed in our most recent article How to build a KPI Excel template with SQL Server.

Healthcare KPI Dashboard in PowerBI

Our thanks to our friends at Tickbox Analytics for creating this great looking dashboard for our use case!

Use Case Background

In the previous article, How to Build a KPI template using SQL Server, we introduced the background to the use case – ACME Health Group, a fictional healthcare company that manages 50 hospitals and healthcare centers across the USA.

ACME collects numerous metrics and produces KPIs to measure and track their performance, but for this use case we are just looking at two:

KPI Type Purpose Definition Target
% Hospital bed occupancy rate Operational Used to indicate the maximum volume of patients that can be admitted to the hospital, representing the hospital inpatient capacity, and also to indicate if beds are under-utilized Measures the percentage of beds that are occupied by inpatients in relation to the total number of beds within the facility. 85%
Staff-to-patient ratio Operational Used to  help healthcare providers assess their resource management strategies and ensure that they have enough staff available to provide quality care to their patients. Measures the number of staff members available per patient in a healthcare facility. 25%

Step 1: Collect Metrics

In order to calculate these KPIs, the 50 healthcare facilities need to collect the following metrics on a regular basis:

  • Beds in Use    
  • Total Beds       
  • Total Staff        
  • Total Patients

We had previously built out a template solution for Health Administrators to collect these data metrics using Excel as the front end and SQL Server for data storage. And then we used the SQL Spreads Add-in for Excel to connect the two.

This is the KPI data collection template that the operational level Health Administrators will use to collect the metrics:

Excel KPI template created using SQL Spreads

With the solution, Health Administrators can:

  • View previous data they have entered for their facilities
  • Capture metrics for new periods and see the KPI’s automatically calculated
  • Select the specific periods and/or regions/facilities (note also that users only see the data they have permissions for)

Step 2: Analyze and Aggregate data in Power BI

Now, let’s build a dashboard in Power BI to display the data visually. This dashboard will be used by management level decision makers to analyse and aggregate the data captured by the operational level Health Administrators. As an example, if there is someone in middle management who needs access to 5 of the hospitals then they should get access to the Power BI dashboard for analysis.

Overview of the KPI Dashboard in Power BI

In this use case, we’re going to create a KPI dashboard in Power BI. The dashboard aims to answer the following questions:

  • What are the current values for the Bed Occupancy and Staff:Patient ratio?
    • across all regions
    • for each facility
  • Are the current values above or below target?
  • How have the KPI values for each facility changed over time? Are there any trends?
  • How does Facility X compare against the average for all locations?

This report uses several different visualization types to answer these questions. In the next section, we’ll go through the steps to create the report.

An overview of a Healthcare KPI Dashboard in Power BI

Creating the Report in Power BI

Step 1: Get Data from SQL Server

To start building your KPI dashboard in Power BI, you’ll obviously need Power BI. If you don’t already have it installed, you can download Power BI for free.

Once installed, open Power BI Desktop from the Home page and then click on the Import Data from SQL Server option.

Add data from SQL Server to Power BI report

In the dialog, enter the Server and Database where the data is located. The Data Connectivity mode defaults to Import – leave it on this option. (The import option loads the data into the Power BI file which makes it quicker to work on, but is not suitable for very large datasets. The Direct Query option has to run queries against the source data each and every time a visualization is refreshed or filter changed)

Click OK.

Selecting a SQL Server database to connect to in Power BI

In the Navigator, you can now select the tables in the SQL database that will be used in the report.  Each one needs to be selected because we’re going to use all of them. We won’t do any transformations in this example, so go ahead and click Load.

Selecting which data tables to add to a report in Power BI

Once the data has been loaded, you will see the tables in the Fields pane on the right-hand side. We also have a blank canvas to work with and the Visualizations pane from which to select our charts and other visualization types.

Starting page for a Power BI report

Step 2: Check Data Model

While loading data from SQL Server in the background, Power BI checks for relationships defined in the data source (i.e., foreign key/primary key relationships). If such relationships exist, Power BI mirrors them into the data model when you initially load data. This option enables you to quickly begin working with your model, rather than requiring you to define those relationships yourself.

To view the data model, click on the Model tab on the left side.  If Power BI doesn’t automatically create relationships, you can add them by clicking on the Manage Relationships menu button.

Data Model in Power BI

Whilst we are checking the relationships in the Data Model, we should also check the Format of the columns that we’ll be working with. For example, we want the KPI_BedOccupancy and KPI_StaffPatientRatio columns to be a Decimal data type, with a format of Percentage. You can check this and change as needed by selecting a column in a table. Then expand the Properties pane on the right-hand side.

Changing the data type of a column in the data model in Power BI

Step 3: Create Visualizations and Report Filters

Let’s look again at the report that we’re trying to build:

An overview of the visualization types in a Healthcare KPI Dashboard in Power BI

It contains the following visual types

  • KPI – to show current KPI values and compare against target
  • Table – to show details at a per facility level
  • Area Chart – to show KPI values and metric totals over time
  • Clustered Column Chart – to show average KPI values per region
  • Slicer – to select the range of weeks to display data for

In the following sections we’ll explain how to add each one.

Adding KPI visualizations to the report

The KPI is a special kind of the Card visualization. It includes a value, a target and a background trend series. The value shown is the most recent one from the data available (which is typically the current period). The visualization automatically calculates the variance from the target and displays a conditionally formatted icon.  The KPI visualization is therefore a great way to add a lot of information to your report with only a few clicks.

Overview of the components of a KPI Visual in Power BI

First, we’re going to add a KPI for % Hospital bed occupancy rate (re-named to ‘Bed Occupancy’).

To configure the KPI visualization, drag the KPI Bed Occupancy column from the FactMetrics table to the Value field in the visualization pane. And then make sure the aggregation is set to Average, not Sum (which is the default). You can do this by clicking on the arrow to the right of the column name and selecting Average.

Selecting the Average aggregation type for a KPI visualization in Power BI

Next, drag the Week column from the DimDate table to the Trend Axis field in the visualization pane.

Finally, we need to add the target value. To do this, we are going to create a new measure. Right-click on the DimKPI table in the Data pane on the right-hand side and click on New measure. In the formula bar above the canvas area, enter the following:

_target_KPI_BedOccupancy = CALCULATE(AVERAGE('DimKPI'[Target]), 'DimKPI'[KPI] = "% Hospital bed occupancy rate")

This formula retrieves the Target value from the DimKPI table. This means that we can modify the target by simply changing it in the database (via SQL Spreads, of course!) instead of having to update the report in PowerBI.

Adding data to a KPI visualization in Power BI
Configure formatting options

There are a couple of formatting options we need to configure to finish off.

  • The first is to make sure the KPI value and icon for the latest period and the background trend are colored correctly to reflect whether they are above or below target. This is done by configuring the Trend Axis section of the Format Visual tab of the Visualizations pane – the Direction value should be set to Low is good
  • The second is to format the Target label that is displayed below the current value – we want it to show a positive variance when it is below the target, so we need to set the Distance Direction in the Target label section to Decreasing is positive.
How to format a KPI visualization in Power BI

We can now go ahead and follow the same procedure to add the KPI visualization for the Staff-to-patient ratio KPI.

Adding a Table visualization to the report

The Table visualization is used to display detailed information. In our example, it will display the following for each facility:

  • No. of beds
  • Average Beds in Use
  • Bed Occupancy KPI – current value
  • Bed Occupancy KPI – trend
  • Average No. of Patients
  • Average No. of Staff
  • Staff:Patient Ratio KPI – current value
  • Staff:Patient Ratio KPI – trend
Example of a Table visualization in Power BI

The configuration of the Table visualization is straightforward as you simply need to select the columns that you want to display. When adding the values, don’t forget to make them Averages (for each week) – this will become even clearer when we add the slicer later.

In addition to the basic data in the table, there are a couple of nice formatting options that have been included in the table to add some visual flair:

  • The trend lines for Bed Occupancy and Staff Ratio are sparklines – these are tiny charts shown within cells of a table or matrix that make it easy to see and compare trends quickly. 
  • The columns showing the current values for the Bed Occupancy and Staff Ratio KPIs include conditional formatting to help highlight specific data.
Adding sparklines to the table

To add a sparkline, click on the dropdown arrow next to the Bed Occupancy KPI column and then click on Add a sparkline. In the Add a sparkline dialog, enter the relevant information and click Create. A new column for the sparkline will be created.

Adding a sparkline to a Table visualization in Power BI

The same process can be done to create a sparkline for the Staff Ratio KPI.

Adding conditional formatting to the table

To add conditional formatting to the Bed Occupancy KPI, click on the dropdown arrow next to column and then click on Conditional Formatting. In our example, the Background Color, Font Color and Icon options have been configured (the screenshot below shows the dialog to configure the Icon display).

Applying conditional formatting to a table visualization in Power BI

The same process can be done to add conditional formatting to the Staff Ratio KPI.

Adding Area Chart visualizations to the report

The Area Chart is basically like a normal Line Chart except that the area below the line is filled in.

The Area Chart is easy to configure – you simply need to choose the columns to use for the X and Y axis and the Legend (i.e. the series that should be plotted, in this case the Location).

The process is the same for both of our KPIs – the screenshots below show the Bed Occupancy KPI.

Configuring the data for an Area Chart visualization in Power BI

The only other thing we want to add to this chart is a constant line to show the KPI target. To add this, click on the Analytics tab in the Visualization pane and then expand the Y-Axis Constant Line and click Add Line. You can now select the KPI target measure that we created earlier (_target_KPI_BedOccupancy) and choose a format for the line.

Configure the target for an Area Chart visualization in Power BI

For the Total Bed Occupancy and Total Staff to Patients area charts, we need to add 2 columns to the Y-axis. Both metrics that are used to calculate the KPI, which in the case of Total Bed Occupancy are the Beds Available and Beds in Use columns

An Area Chart visualization in Power BI

Adding a Clustered Column Chart visualizations to the report

This visualization shows the values for the two KPIs averaged across the 3 regions. But it also functions as a useful filter for the report. By clicking on one of the bars for one of the regions, it filters the data in the report to only show values for locations in that region.

The Clustered Column Chart is also easy to configure. You simply need to specify the columns to use for the X and Y axis as shown below:

Configuration of a Clustered Column Chart visualization in Power BI

Adding a Slicer to the report

You can use the slicer to filter the report and show data between a selected range of weeks. In order to show the entire year, for example, you can set the slicer to a range of 1 to 52.

Adding a Slicer to a Power BI report

Adding a Title to the report

Finally, we need to add a title for the report which is simply a text box and some images.

The title of a dashboard in Power BI

Summary

In this article, we’ve gone through the steps to create a KPI dashboard in Power BI. This builds on the previous article where we built a KPI collection system in Excel using SQL Server to store the data. Then used SQL Spreads Excel Add-in to connect to and maintain the data.

The Power BI dashboard uses several different visualization types to provide insight into the KPI data for our hypothetical Healthcare organization, ACME Health Group. With this kind of report, decision makers can get a quick overview of performance across the group. But also at an individual location level to dig a little deeper to spot trends or anomalies.

The overall stack of SQL Server, Excel, Power BI and SQL Spreads is a great solution with the following advantages:

  • Users (in our case, Health Administrators and Managers) can capture performance data in a simple Excel workbook
  • With SQL Spreads, the process of data entry in Excel is secure and controlled through data validation and other checks. This greatly reduces the chance of data entry errors
  • Data is stored in a best-in-class database management system, SQL Server
  • The Power BI features and ease of use make it possible to build quick and simple reports and dashboards. Or ones with more complexity as needed
This image has an empty alt attribute; its file name is Phil-Curtis-1024x1024.png

Power BI Dashboard by:

Phil Curtis from Tickbox Analytics, has decades of client work with SQL Server, MS Access (but now preferring SQL Spreads!), developing Power BI analytics and new integrations with Python. Works with SQL Spreads for easy but powerful SQL Server data management.

For more information on using Excel, SQL Server and Power BI to create a KPI dashboard in Power BI, or general questions on your data management systems, contact us. If you haven’t already tried SQL Spreads, download a free trial and see how easy it is to manage SQL data from within Excel.

Leave a Reply


No comments yet. Be the first!

Try SQL Spreads for free

Try SQL Spreads during 14 days for free. The free trial is the fully
functional, time-limited only product.
Try Excel Add-In for Free download-image
stars-image
14-days Free Trial. No credit card required.