How to Create a Scorecard in Power BI

Tired of hunting through dense reports to find out if you're hitting your targets? A clear, simple scorecard can give you that at-a-glance health check you need. This tutorial will walk you through, step-by-step, how to build a powerful and visually appealing KPI scorecard directly in Power BI.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is a Scorecard in Power BI?

Think of a scorecard as your business's report card. It's a focused visual that tracks your most important key performance indicators (KPIs) against their pre-defined goals. Unlike a comprehensive dashboard that might explore data from many angles, a scorecard has one job: to quickly tell you how you’re performing. Are you winning (green), getting close (yellow), or falling behind (red)?

For example, a marketing scorecard might track:

• Website Traffic: 45,000 Visitors (Target: 50,000)
• New Leads: 950 Leads (Target: 1,000)
• Cost Per Lead: $48 (Target: $45)
• Conversion Rate: 2.1% (Target: 2.5%)

Each metric is clearly displayed with its actual value versus its target, often with a color-coded status indicator. It’s the fastest way to get a pulse on what matters, focusing your team's attention on the results, not just the raw data.

Before You Begin: Prepping Your Data for Success

An effective scorecard starts with well-structured data. Taking a few minutes to organize your information upfront will make the building process in Power BI infinitely smoother. This isn't a complex technical step, it's about thinking clearly about what you want to measure.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

1. Identify Your Key Performance Indicators (KPIs)

First, decide what you need to track. Don't fall into the trap of measuring everything. A good scorecard has a handful of truly "key" indicators. To find yours, ask yourself and your team:

• What are our primary goals for this quarter?
• What numbers tell us if we are on the right track to meet those goals?
• If I could only look at 3-5 metrics, what would they be?

Here are some examples to get you thinking:

• Sales Team: Monthly Recurring Revenue (MRR), Win Rate, Average Deal Size, Sales Cycle Length.
• Marketing Team: Marketing Qualified Leads (MQLs), Customer Acquisition Cost (CAC), Website Conversion Rate, Return on Ad Spend (ROAS).
• E-commerce Store: Average Order Value (AOV), Cart Abandonment Rate, Customer Lifetime Value (CLV), Total Orders.

2. Structure Your Data Table

Once you have your KPIs, you'll want to structure them in a simple table. This is the data source Power BI will use. Whether you're using Excel, Google Sheets, or a database, the format is the same. Your table should have, at a minimum, three columns:

• KPI Name: The name of the metric (e.g., "Sales Revenue").
• Actual Value: The current, actual number for that metric.
• Target Value: The goal you are aiming for.

Here’s what a simple table in Excel or Google Sheets might look like:

With this clean, simple structure, you're ready to jump into Power BI.

Step-by-Step Guide: Building Your Power BI Scorecard

Now for the fun part. Let's open up Power BI Desktop and turn that simple data table into a dynamic scorecard. We’ll start simple and then add more sophisticated features.

Step 1: Get Your Data into Power BI

First, you need to connect to your data source.

1. On the Home tab in Power BI, click Get data.
2. Select the appropriate source. For our example, we’ll use Excel workbook.
3. Navigate to your file, select it, and click Open.
4. In the Navigator window, check the box next to your data table (e.g., 'Sheet1') and click Load.

You'll see your table fields ('KPI', 'Actual', 'Target') appear in the Data pane on the right-hand side.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Use Individual "Card" Visuals for Each KPI

The simplest way to display a big, bold number is with the Card visual. We will create one card for each of our KPIs.

1. In the Visualizations pane, click on the Card visual icon (it looks like a tag with "123" on it).
2. A blank card will appear on your report canvas. With the new card selected, go to the Data pane and drag your 'Actual' field into the "Fields" well of the visual.
3. Right now, this card shows the sum of all your 'Actual' values, which isn't what we want. We need to filter it to show only one KPI. With the card still selected, drag the 'KPI' field into the "Filters on this visual" section of the Filters pane.
4. Select the name of the first KPI you want to display, for example, "Monthly Visitors."

Now, repeat this process for each of your other KPIs. Just copy and paste the card you just made, then change the filter for each new card to display the next KPI. You should now have a series of cards, one for each of your key metrics.

Step 3: Creating DAX Measures for Color-Coding

Here’s where your scorecard comes to life. We're going to use conditional formatting to automatically color the numbers based on performance. The most reliable way to do this is with a simple DAX measure. A measure is essentially a formula that performs a calculation. We'll create one that determines the color based on whether the 'Actual' value meets, misses, or is close to the 'Target' value.

1. In the Home tab, click New measure.
2. A formula bar will appear. Copy and paste this DAX formula in. This formula says: if the actual value is greater than or equal to the target, make it green. If it's within 90% of the target, make it orange. Otherwise, make it red.

KPI Status Color = 
VAR ActualValue = SELECTEDVALUE('YourTableName'[Actual])
VAR TargetValue = SELECTEDVALUE('YourTableName'[Target])
RETURN
    IF(
        ActualValue >= TargetValue,
        "#01B8AA", // Green
        IF(
            ActualValue >= TargetValue * 0.9,
            "#F2C80F", // Orange
            "#FF6B6B"  // Red
        )
    )

Make sure to replace ‘YourTableName’ with the actual name of your data table. Press Enter to save the measure.

Pro Tip: For metrics like "Cost Per Lead" where a lower number is better, you’ll need to reverse the logic (e.g., ActualValue <= TargetValue is good). You can create a second DAX measure specifically for these types of "lower is better" KPIs.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Applying Conditional Formatting

Now let's apply that color measure to our cards.

1. Select your first KPI card (e.g., "Monthly Visitors").
2. Go to the Format your visual pane (the paintbrush icon).
3. Expand the Callout value section.
4. Find the Color property and click the small fx (Conditional formatting) button next to it.
5. A dialog box will pop up. In the "Format style" dropdown, select Field value.
6. In the "What field should we base this on?" dropdown, find and select your new measure, "KPI Status Color."
7. Click OK.

The number on your card should instantly change color based on its performance against the target! Repeat this formatting step for all your other cards.

Design Tips for a High-Impact Scorecard

Technology is only half the battle. A good design makes your scorecard intuitive and easy to read. Here are a few tips:

• Use a Matrix or Table for the Full Picture: While individual cards are great for headline numbers, putting everything in a single, well-formatted Matrix or Table visual provides context. You can show the KPI name, target, actual, and variance all in one clean view.
• Add Sparklines for Trends: Add a column with a sparkline to your matrix to show the performance trend over time. Is the number red and trending down? That might require more urgent attention than a red number that is trending up.
• Keep it Clean: The purpose of a scorecard is clarity. Avoid clutter, excessive colors beyond the status indicators, and distracting background images. White space is your friend.
• Group Logically: Arrange your KPIs in a logical order. You might group all your marketing funnel metrics together or place your main "North Star" metric at the very top.
• Leverage Titles & Labels: Change the generic title of your cards to be more relevant. Labeling titles with actual metric names provides the helpful context everyone needs.

Final Thoughts

Building a scorecard in Power BI transforms your raw data into a clear story about business performance. By focusing on your most critical KPIs and using straightforward visual cues like color, you can create a powerful tool that keeps your entire team focused on what truly matters to move the needle.

Creating reports and dashboards, even in powerful tools like Power BI, can still feel like a manual process with a steep learning curve. At Graphed, we’ve built a solution to remove that friction. We allow you to connect all your data sources and create stunning, real-time scorecards and dashboards just by describing what you want to see in plain English. This approach saves countless hours and empowers every team member, not just data experts, to get instant answers and track performance effortlessly.