KPIs and Cards in Power BI: Designing Scorecards

A headline number on its own tells a reader almost nothing. Sales of 29.76 million is good news if last year was 21.93 million and bad news if the target was 35. KPI cards and scorecards exist to put a small number of important figures in front of decision makers, and their design is mostly about context: compared with what, and is that good?

Three tools for headline numbers

Power BI offers three ways to present KPIs, suited to different jobs:

Microsoft describes a KPI as a visual cue that communicates progress toward a measurable goal, and the KPI visual needs a base measure, a target and a threshold. The card visual can show several cards in one visual, each with reference labels, which makes it the usual building block for a KPI row. Scorecards are a service feature; authoring and sharing them requires a Pro licence.

Every number needs a comparison

Before placing a card, decide what it is compared with:

• The same period last year, which removes seasonality.
• A target or budget, which shows whether the plan is on track.
• The previous period, for fast-moving operational metrics, read with the season in mind.

Here is a worked KPI row from Microsoft's Financial Sample (Microsoft Learn: Financial Sample). The sample's data starts in September 2013, so the fourth quarter is the only one with a year-earlier comparison:

import io
import urllib.request
import pandas as pd

url = "https://go.microsoft.com/fwlink/?LinkID=521962"
finance = pd.read_excel(io.BytesIO(urllib.request.urlopen(url).read()), keep_default_na=False)
finance.columns = finance.columns.str.strip()
finance["Quarter"] = finance["Date"].dt.to_period("Q")

def kpis(rows):
    return {"Sales (m)": rows["Sales"].sum() / 1e6,
            "Profit (m)": rows["Profit"].sum() / 1e6,
            "Gross margin": rows["Profit"].sum() / rows["Sales"].sum(),
            "Discount rate": rows["Discounts"].sum() / rows["Gross Sales"].sum(),
            "Units (k)": rows["Units Sold"].sum() / 1e3}

current = kpis(finance[finance["Quarter"] == pd.Period("2014Q4")])
prior = kpis(finance[finance["Quarter"] == pd.Period("2013Q4")])
cards = []
for name in current:
    is_rate = name in ("Gross margin", "Discount rate")
    change = (current[name] - prior[name]) * 100 if is_rate else (current[name] / prior[name] - 1) * 100
    cards.append({"KPI": name,
                  "Q4 2014": f"{current[name]:.2%}" if is_rate else f"{current[name]:.2f}",
                  "Q4 2013": f"{prior[name]:.2%}" if is_rate else f"{prior[name]:.2f}",
                  "Change": f"{change:+.2f} pts" if is_rate else f"{change:+.1f}%"})
print(pd.DataFrame(cards).to_string(index=False))

          KPI Q4 2014 Q4 2013    Change
    Sales (m)   29.76   21.93    +35.7%
   Profit (m)    4.41    3.11    +41.7%
 Gross margin  14.83%  14.20% +0.62 pts
Discount rate   6.33%   7.97% -1.64 pts
    Units (k)  263.47  214.07    +23.1%

Two details matter. Changes in rates are shown in percentage points, not percentages: margin rose from 14.20% to 14.83%, a 0.62-point increase. And the direction of "good" differs: a falling discount rate is good news, so its arrow should be green even though the number went down.

Measures behind the cards

Sales PY = CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Sales vs PY % = DIVIDE ( [Sales] - [Sales PY], [Sales PY] )
Margin PY = CALCULATE ( [Gross Margin %], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
Margin vs PY pts = ( [Gross Margin %] - [Margin PY] ) * 100
Discount Status =
    VAR Change = [Discount %] - CALCULATE ( [Discount %], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
    RETURN IF ( Change <= 0, "#2E8B57", "#C0392B" )    -- lower is better

The status measure returns a colour, which can drive the card's font or an icon through field-value conditional formatting; conditional formatting in Power BI shows how. Keep one status measure per KPI, so the rule for "good" is written down once.

Decide the direction of good for each KPI

Writing this table down before building anything settles arguments later and makes the status measures straightforward.

When there is nothing to compare with

The first period in the data has no prior year, and a new product has no target. A change measure built with DIVIDE returns blank in those cases rather than an error or a misleading 100% increase. Show that blank as "n/a" or leave the comparison out, and never replace it with zero, which would read as a real change.

Label the period dynamically

Cards change when users move slicers, so a fixed subtitle such as "Q4 2014" soon becomes wrong. Use a measure that builds the label from the current selection, for example with SELECTEDVALUE on the date table's quarter column, and use it as the visual's subtitle through the title's conditional formatting.

Designing the card row

• Four to six cards, in the order readers think: volume, value, efficiency.
• Value large, comparison small, with the comparison labelled, for example "vs Q4 2013".
• Scale and round: "29.8m", not "29,757,840.25".
• Use colour for status only, and pair it with an arrow or sign so it does not rely on colour alone.
• Keep the same order and formatting on every page and report.
• Say what period is shown, in a subtitle, especially when filters can change it.
• Test with slicers applied, including a selection with no data, to see what every card shows at the edges.

The rest of the page follows from the cards; Power BI dashboard design covers layout, and choosing which KPIs to show at all is covered in how to create effective KPIs.

When to use scorecards instead

Cards summarise a report's data. Scorecards are for managing performance: Microsoft describes them as a way to curate goals and track them against key business objectives in a single view, with check-ins recording progress over time. Use them when a team needs to track and discuss progress against objectives over months, not just see this quarter's numbers.

Common mistakes

• Cards without comparisons.
• Percent change on rates, instead of percentage-point change.
• Red and green without meaning, such as colouring every decrease red when some decreases are good.
• Too many cards, which makes none of them stand out.
• Mixed periods, such as year to date next to a single month, without labels.

KPI cards, charts and dashboard design are part of the data visualisation and dashboard design modules of the Power BI course.

Sources

• Create a card visual in Power BI (Microsoft Learn) — Microsoft Learn
• Key Performance Indicator (KPI) visuals (Microsoft Learn) — Microsoft Learn
• Get started with scorecards in Power BI (Microsoft Learn) — Microsoft Learn
• Download the Financial Sample Excel workbook for Power BI (Microsoft Learn) — Microsoft Learn