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.
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:
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.
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.
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.
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.
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.
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.
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.
Step 3: Create Visualizations and Report Filters
Let’s look again at the report that we’re trying to build:
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.
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.
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.
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.
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
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.
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).
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.
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.
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
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:
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 Title to the report
Finally, we need to add a title for the report which is simply a text box and some images.
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
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.
