In this Microsoft Power BI Tutorial, we will learn about Power BI KPI visual. Also, we will discuss:
- What is KPI in Power BI?
- When to use a KPI and Requirements for KPI visual?
- How to create a KPI in Power BI Desktop?
- Power BI KPI visual format
- Power BI KPI visual trend axis and without trend
- Power BI KPI matrix
- Power BI KPI dashboard example
- Power BI KPI target goal
- Power bi KPI custom visual
- Benifits of using KPI visual
What is KPI in Power BI?
The KPI visual can be created and viewed in Power BI desktop and service in Power BI. A Key Performance Indicator (KPI) visual is used to measure the value that demonstrates how a business achieves its goal at reaching the target.
A KPI displays the progress towards the goal in a graphical way.
Organizations use KPIs at multiple levels to evaluate their accomplishment. A high-level-KPIs focus on the whole performance of the enterprises, where a low-level-KPIs focus on processes in a particular department such as sales, profits, marketing, etc.
A KPI is based on a specific measure and is designed to help us demonstrate the current value and status of a metric against a defined goal.
Here is an example of a KPI visual on Power BI Desktop:
This is how a KPI visual looks like. Basically, it is based on the actual progress vs the target by the trend line.
When to use a KPI and requirements for KPI visual?
KPIs are a great choice:
- To measure the progress.
- To measure the distance to a goal.
Requirements for KPI visual:
A KPI visual helps to evaluate the current value and status of a metric against a defined target.
A KPI data set needs to contain goal value for a KPI. If it doesn’t contain a goal value, then we can create it by adding an excel sheet to our data model.
Creating a KPI visual requires a base measure that evaluates to a value, a target measure or value, and a goal.
A Power BI KPI needs 3 values:
- Actual (Indicator)
- Target (Goal)
- Trend (Month or some sort of metrics)
How to create a KPI in Power BI Desktop?
Here is a step-by-step guide to creating a KPI visual in Power BI Desktop:
On Power BI Desktop, load the sample data using the get data option. Your sample data may be an excel sheet, text file, SharePoint data, SQL data, etc.
Lets;’s create a stacked column chart using sample data. For example, here we create a chart that visualizes the data as current year sales over months like below:
Now we will sort this visual by month. Because once we create a KPI visual, there is no option to sort the visual. To sort the visual:
Go to More option(…) > sort by(Sort ascending) > month name
Once sorted(alphabetically) correctly, the visual will look like this:
Convert this column chart to a KPI visual by selecting the KPI icon under the visualizations pane. To add a goal, drag last year’s sales column to the target goals field.
This is how to create a KPI visual on Power BI Desktop. Also, it is available on Power BI Service and our mobile devices. It gives an option to be always connected to our business.
Power BI KPI visual format
After creating KPI visual on Power BI Desktop successfully, now we will see how to format this visual. To get the formatting options, click on the paint roller icon.
The most notable ones are:
- Indicator– It controls the indicator looks such as: text size, alignment, display unit, decimal palces, font family, etc.
- Trend axis– When we set to On, the trend axis displayed as the background of the KPI visual. By toggling to off we can turn off to hide the trend axis on visual.
- Goals– When set to on, the visual displays the goal and it’s distance as a percentage. We can show or hide the goal on the visual by it’s respective togggle. Also, we can customize the label, it’s color, font family, text size, and few others.
- Color coding > Direction– The color coding setting is one of the most important format. This setting let us change the direction, whether high is good or low is good. Also, we can change the colors naturally.
For example, changing the direction from ‘High is good’ to ‘low is good. Then the chart will change its color from green to red like this:
This is how to do format the KPI visual on Power BI Desktop.
Power BI KPI visual trend axis and without trend axis
When set to ON, the trend axis visualize as the background of the KPI visual. The graph incorrectly suggests we have a dramatic increase and decrease each month. Also, it allows adjusting its transparency.
For example, we will set the transparency from 20% to 35%, then the trend axis will appear darker.
Similarly, by turning off the trending axis’s toggle, the trending axis will disappear from the visual like this:
This is what a trend axis and without trend axis on Power BI KPI visual.
Power BI KPI target goal
A Power BI KPI visual requires a base measure that evaluates to a value and a target measure and a target goal.
A KPIs are the critical indicators of progress towards an intended result. Currently, a KPI dataset needs to contain a goal value for creating KPI, if it does not contain then we can create goals by adding them to our data model.
Have a look at this example, that we previously created:
The above chart, showing the target in a value and a percentage. The target is how much we want to get done. A .’+’ sign indicates the increasing value where a ‘-‘ sign indicates the decreasing value towards the target goal.
Here, it showing a positive sign because, in our data, the current year’s sale is greater than our target goal i.e. last year’s sale.
Power BI KPI dashboard example
A KPI dashboard creates a real-time visualization (on mobile, desktop, or tablet)of our selected KPIs. The best KPIs dashboards are customizable, allowing us to, among other things, change colors, organize our KPIs, and see our progress in a single glance.
KPI dashboards are tools that gathered the data sources and provide at-a-glance visual feedback showing how our business is performing against our KPIs. Users get benefits by using:
- A fast and easy solution to tracking KPIs and other business metrics.
- Monitoring the company’s financial health
- Measure progress against strategic goals.
- Make better decision faster.
Effective KPI dashboards bring together all the KPIs that we need to track our strategic goals, establishing a visual representation of all our relevant metrics side by side, in one place.
Microsoft provides us some KPI Dashboard examples from where we can consider some of the key associated metrics and we would likely want to track:
- Marketing effectiveness
- Keyword performance
- Average time on page
- Average lead score
- Customer service
- Cost per call
- First response time
- Average solution time
- Financial health
- Profit and loss
- Current ratio
- Burn rate
- Vendor expenses
- IT performance
- IT ROI
- Mean time to repair
- Project delivered within budget
- Server downtime
Power BI KPI Matrix
Now we will see how a Power BI KPI Matrix works. The tabular format of Power KPI Matrix allows for an unlimited number of metrics and KPIs in a single list.
Balance scorecards. It represents different types of metrics and non-additive Key Performance Indicators, such as: financial, operational, and customer-focused measures, like rows in a single list.
It is super flexible and customizable. Control fine details such as the font and background color for each cell, row, column, number format, etc.
Now we will see how to create a Power BI KPI Matrix with a step-by-step guide.
As it is a custom KPI visual, so we have to download it from the Microsoft app source.
Under visualizations > Click on three dots (…) > to get more visuals.
On the report page, click on this matrix visual. Then add the data field from the data model.
For example, here we will add these fields:
- Date: Month name
- Actual value: current year sale
- Comparison value: Last year sale
- KPI Indicator index: Color status
- Row-based matric name: Segment(department)
Here we created a measure as color status, which indicates the KPI status with a specific indicator such as arrow up, triangle, circle, square, arrow down, etc.
Color Status = IF(SUM(financials[current year sale])>SUM(financials[Last year sale]),1,IF(SUM(financials[current year sale])<SUM(financials[Last year sale]),2,IF(SUM(financials[current year sale])>=SUM(financials[Last year sale]),3)))
Here the numerical number(i.e. 1,2,3) indicates the symbol of the KPI status indicator. We can customize that symbol and its color in the KPI indicator of the KPI Matrix’s format pane.
After adding these fields to the visual, the matrix visual look like:
According to our data, all the value increases from last year’s sales to the current year’s sales, so the KPI Status column showing similar indicators.
Here we customize the sparklines to black background for visual attraction.
This is how to do Power BI KPI Matrix.
There are some key features of using Power BI KPI Matrix:
- Pivot output metrics as columns or rows.
- custom sparkline which show the available trend of actual value.
- Expandable categories which show or hide groups of metrics by using the category features.
- KPI indicators. Choose from various KPI symbols to represent performance realtive to a target in the data model.
- Auto table formats, choose from a selection of preset table theme.
- Granular control of background colors, and font colors, size, style, vertical, horizontal, and more.
- Pop-Out Power BI KPI chart, by clicking any non-hyperlinked cell launches to Power KPI chart to show full trend details.
Power BI KPI custom visual
We can do more interesting things with KPI. For example, we can use many variations of the default KPI. We can get this custom visual from the Microsoft app source.
Under visualizations > Click on three dots (…) > to get more visuals.
Here we discussed 3 important custom KPI visuals that we didn’t know we needed.
- Power KPI- This visual is used to presenting a key performance indicator, along with a veriety of supporting information such as: current dates, values, variances. This visual shows the data as:
- KPI Indicator- This visualization is all about visualizing Power BI key performance indicators. Unlike the default KPI, this custom Power BI KPI represents the status as a color indication, comparing the actual with target values. The trend could be visualized as a line or a bar chart. This visual represents the data as:
- Power KPI Matrix- Power KPI Matrix balanced the scorecards in Power BI and displays in an unlimited number of metrics and KPIs in a single, customized list. It represents a tabular format that allows for an unlimited number of metrics and KPIs in a single list. The Power KPI Matrix visual represents the data as:
Benifits of using KPI visual
Let’s have a look at the benefits of using KPI visuals, which track and analyze the assets information for smart decision making:
- Measuring popularity.
- Identifying trends.
- Customer response.
- To access performance of current state.
- To judge the quality of the requirements gathered.
- To determined whether business objectives have been achieved.
- Motivate team member.
- Make timely adjustments to tactics.
You may also like the following Power BI tutorials:
- Power bi row level security tutorial
- Power BI Funnel Chart
- Power BI waterfall chart
- Microsoft Power BI Stacked Column Chart
- Power Bi Schedule Refresh
- Power Bi Pyramid chart – How to use
- How to create a filter in Power bi
Here, we learned all about Power BI KPI visuals. Also, we discussed:
- What is KPI in Power BI and when to use a KPI and it’s requirement?
- How to create a KPI in Power BI Desktop?
- How to do format on Power BI KPI visual?
- How to show the trend axis and without trend on Power BI KPI?
- What is Power BI KPI Matrix with example?
- What are the examples of Power BI KPI dashboard?
- What is target goal in Power BI KPI?
- What are the custom visuals of Power BI KPI?
- Disscuss about benifits of using KPI visual.
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com