A Step-By-Step Guide to Visualizing KPIs in Power BI

Choosing Your KPIs

Always ensure you have clear, measurable goals before you add KPI visuals to your dashboards. Your KPIs should closely align with the company's business objectives.

Less is more when it comes to KPIs. Avoid adding too many KPI visuals to your dashboards.

We'll consider a common KPI for many businesses: profit margin. We also set an arbitrary target of 15% for our profit margin.

Step-by-Step Guide to Creating KPI Visuals in Power BI

We'll visualize our profit margin KPI in three ways:

• Using the KPI visual
• Using the gauge visual
• Using the card visual with a few creative adjustments

Step 1: Get your data ready

The first step is to ensure that your data is clean, reliable, and structured so that Power BI can digest it.

Getting your data ready often involves some data prep work, such as:

• Importing data into Power BI Desktop. This could be from Excel, a database, or any other data source.
• Performing data transformation as needed to clean up duplicates, missing values, data types, extra columns, and so on.
• Setting up the data model, with relationships defined if you're using multiple tables.

Luckily, this sample data is cleaned and ready to use, so we'll import it into Power BI and start visualizing our KPI.

Step 2: Create your measures

We need to create measures for the base and target values of the KPI and gauge visuals (and two other measures for our card visual). Measures are formulas that you define using DAX (Data Analysis Expressions).

To get started with DAX, check out our Introduction to DAX course and keep our DAX cheat sheet on hand for a quick reference on many of the most useful DAX functions you will encounter (including the ones we’ll use in this tutorial).

The DataCamp DAX Cheat Sheet

Profit margin is calculated as profit divided by total sales. Here is the formula we use:

Profit Margin = SUM ( Profit ) / SUM ( Sales )

Our target measure is a simple scalar:

Profit Margin Target = 0.15

For the card visual, we must create two additional measures.

The first measure calculates the difference between the actual profit margin and the target. Then, it uses the FORMAT function to effectively convert the numbers to text so that we can concatenate a few other strings to make our “Goal” line, as seen in the KPI visual.

KPI Profit Margin vs Target =
  VAR Diff = [Profit Margin] - [Profit Margin Target]
  VAR Sign = IF(Diff > 0, “+”, “”)
  RETURN
  “Goal: “ & FORMAT([Profit Margin Target], “#0%”) & “ (“ & Sign &  FORMAT(Diff, “#0.0%) & “)”

The second measure controls the color of our card. You’ll see that we use the SWITCH function in this expression. If you’re new to SWITCH and want to learn more about how it works and how you can use it, check out our guide to the Power BI SWITCH function.

KPI Color =
  VAR Diff = [Profit Margin] - [Profit Margin Target]
  RETURN
  SWITCH (
    TRUE(),
    Diff > 0, “Green”,
    Diff < 0, “Red”,
    “Black” )

Step 3: Add the KPIs to the report

KPI visual

In Power BI Desktop, go to the "Report" view. From the "Visualizations" pane, select the KPI visual.

The Power BI KPI visual contains these three fields:

• A base value: This is your actual data point, like current sales figures.
• A target value: The goal or benchmark you're aiming for, such as sales targets.
• A trend field: Most effective KPIs are tracked over time, so having date data is crucial.

Image by author

Gauge visual

From the "Visualizations" pane, select the gauge visual.

The gauge visual is much more flexible than the KPI simply because it offers more fields for adding measures that control the minimum and maximum values on the gauge. You are also not obligated to add a date column for showing a trend, which makes the gauge visual more suited to KPIs that may not need to be tracked over time.

Image by author

Card visual

Back in the "Visualizations" pane, we are now going to select the card visual.

Typically, this visual shows a single summarized metric. However, with the formatting and customization options, you can get creative with the type of information you can show with a card visual.

We will recreate the KPI visual using a few measures (that we already created in step 2 above) and some customization options in a card visual.

Here is the final product:

Image by author

The card visual is my first choice when visualizing KPIs in scenarios where I need more flexibility. What if I don’t need to track my KPI over time and don’t have a date column in my data? What if I want to add a custom calculation showing the difference between the actual value and the target?

One key difference between our card here and the KPI visual that we created earlier is that the KPI visual always calculates the percentage change between the actual value and its target. This is unintuitive for percentage-based KPIs since it makes more sense to calculate a simple difference between the actual value and the target.

With the card visual, we can choose how we want the KPI to be calculated, displayed, and compared against our target.

If you're interested in exploring advanced analytical features in Power BI, we have a tutorial to guide you through the process.

Best Practices for KPI Reporting

Keep it simple: Don't overload your KPI with too much information. Focus on a few key KPIs that reflect your current business objectives. Explore other visualization options or use drill-down features to allow users to explore more detailed data without overwhelming the main dashboard view.

Check out our course on data visualization in Power BI to learn more about how to visualize and present your insights to your users effectively.

Use color wisely: Colors can draw attention to key information or trends. For example, red could indicate performance below targets, while green shows areas of success. If your users are color-blind, you can also select from color palettes that are color-blind-friendly. Just be sure to communicate the meaning of the colors to your users.

Use your KPIs to tell a story: Arrange your KPIs logically, guiding the viewer through a narrative. Highlight insights and takeaways to draw attention to the most important aspects of the data.

Check out these 9 dashboard examples to get inspiration on the different ways you can tell a compelling story with your data in Power BI.

For a quick tutorial on designing engaging Power BI reports, we’ve got you covered on that too.

Align KPIs with business objectives: Ensure each KPI is directly linked to a strategic business objective. This alignment guarantees that the insights gained from the KPIs are relevant and actionable.

Balance leading and lagging indicators: Include both leading indicators (which predict future performance) and lagging indicators (which reflect past performance) to get a comprehensive view of your business.

Regularly review and update KPIs: It's important to review KPIs regularly to ensure they remain relevant. Update your KPIs to reflect changes in strategy, market conditions, or operational focus.

Conclusion

This tutorial explored three different types of Power BI visuals that can be used to visualize KPIs: the KPI visual, the gauge visual, and the card visual. When visualizing your own KPIs, consider the best practices for KPI reporting that we discussed here, such as keeping it simple and focusing on a few key metrics.

Following these guidelines can help your company easily monitor their most important metrics, track progress toward its targets, and identify opportunities or risks.

If your goal is to pursue a career in Power BI, obtaining certification is an excellent method to enhance your resume. Our all-inclusive Data Analyst in Power BI career track teaches you everything you need to know to secure your first job with Power BI and thoroughly equips you to earn certification (check out this webinar, where we give an in-depth overview of this career track and what you can expect).