How to Build a Sales Dashboard in Power BI

A sales dashboard in Power BI tracks a small set of KPIs, revenue, win rate, pipeline coverage, and average order value, built on a clean model of CRM and order data. The build order matters: agree the metrics with the sales team first, model the data second, and design the visuals last, not the other way around.

For the general steps of building any dashboard in Power BI, see our guide to creating a dashboard in Power BI and our dashboard design guide. This post stays on the metrics and the model specific to sales.

The KPIs a sales dashboard tracks

• Total Revenue. Closed sales value for the period.

• Sales Growth. The percentage change in revenue versus a prior period.

• Average Order Value (AOV). Revenue divided by number of orders.

• Win Rate. The share of closed opportunities that were won, out of all opportunities that closed either won or lost.

• Pipeline Coverage. The value of open pipeline compared to the remaining quota for the period.

Win rate and pipeline coverage are the two most often missing from a sales report built outside a proper data model, because both need opportunity-level CRM data, not just closed-order totals.

The data model

The Stage column needs to tell three states apart: won, lost and still open. Most CRMs use stages named Closed Won and Closed Lost for the first two. Win rate should only count opportunities that have actually closed, so an open opportunity does not silently drag the ratio down.

Core measures in DAX

Total Revenue =
SUM('Orders'[Amount])

Sales Growth % =
VAR PriorRevenue = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
DIVIDE([Total Revenue] - PriorRevenue, PriorRevenue)

Average Order Value =
DIVIDE(
    [Total Revenue],
    DISTINCTCOUNT('Orders'[OrderID])
)

Win Rate % =
DIVIDE(
    CALCULATE(COUNTROWS('Opportunities'), 'Opportunities'[Stage] = "Closed Won"),
    CALCULATE(
        COUNTROWS('Opportunities'),
        'Opportunities'[Stage] IN { "Closed Won", "Closed Lost" }
    )
)

Open Pipeline Value =
CALCULATE(
    SUM('Opportunities'[Amount]),
    NOT 'Opportunities'[Stage] IN { "Closed Won", "Closed Lost" }
)

Pipeline Coverage =
DIVIDE(
    [Open Pipeline Value],
    SUM('Quota'[QuotaAmount])
)

Pipeline Coverage should almost always be filtered to the current period's quota, since comparing open pipeline against a full year of quota when only a quarter remains will understate how thin coverage actually is.

Report layout

1. Overview. Cards for Total Revenue, Sales Growth, Win Rate, and Pipeline Coverage, with a trend line of revenue by month.

2. Pipeline. A table of open opportunities by stage and rep, with Pipeline Coverage broken out by region.

3. Rep performance. A matrix of reps against Win Rate, Total Revenue, and AOV, so a sales manager can see which reps are closing well versus which are generating volume without closing it.

Two interactions are worth adding once the base pages work:

• A drill-through from the rep matrix to that rep's open opportunities.

• Tooltips on the trend line showing the prior period's number for quick comparison.

FAQs

What are the essential elements of a good sales dashboard?

The KPIs above, a data model that supports them at the right grain, and a layout that puts the headline numbers first with detail available on drill-through. For general layout and formatting principles that apply beyond sales, see our dashboard design guide.

How is win rate different from a close rate shown as a percentage of all leads?

Win rate compares won opportunities only against opportunities that actually closed, won or lost. A rate calculated against all leads, including ones still open, mixes a measure of sales effectiveness with a measure of pipeline volume, and moves for reasons that have nothing to do with how well a rep is closing deals.

Sources

• DIVIDE function (DAX) - Microsoft Learn

• SAMEPERIODLASTYEAR function (DAX) - Microsoft Learn