Power BI Sales Dashboard: KPIs, Metrics, and Templates

Sales teams that track the right KPIs in real time close 28% more deals, according to Salesforce's State of Sales report. Yet most sales dashboards fall into two failure modes: either they track vanity metrics that feel good but don't drive decisions, or they overwhelm reps with 40+ numbers that create analysis paralysis. A well-designed Power BI sales dashboard shows the right 15-20 metrics at the right level of detail, with drill-through to investigate anomalies.

This guide builds a complete Power BI sales dashboard architecture from data model to published report — covering every KPI with its DAX formula, the layout logic behind the design, and drill-through patterns that let managers explore without switching tools.

Key Takeaways

• 15 essential sales KPIs with complete DAX formulas ready to implement
• A 4-page dashboard architecture covering Executive Summary, Pipeline, Rep Performance, and Trend Analysis
• Drill-through design pattern to navigate from aggregate KPIs to individual deal details
• Dynamic targets: load quota data from a separate table and compare vs actuals in DAX
• Conditional formatting rules to flag at-risk deals, under-performing reps, and pipeline gaps
• Bookmark-based navigation for clean user experience without header clutter
• The star schema data model required before building any of the above
• Time intelligence patterns: rolling 30-day, MTD, QTD, YTD, and prior period comparisons

---

Data Model Design

Before writing a single DAX measure, the data model must be correct. Sales dashboards typically require these tables:

Fact Tables

Fact_Opportunities (one row per deal):

• OpportunityID, AccountID, OwnerID, StageID, CloseDate, Amount, Probability, Created Date, Type

Fact_Activities (one row per activity):

• ActivityID, OpportunityID, OwnerID, ActivityDate, ActivityType, Duration

Fact_Quotas (one row per rep per period):

• OwnerID, PeriodID, QuotaAmount

Dimension Tables

Relationships

Fact_Opportunities → Dim_Account (AccountID)
Fact_Opportunities → Dim_Owner (OwnerID)
Fact_Opportunities → Dim_Stage (StageID)
Fact_Opportunities → Dim_Date (CloseDate) [active]
Fact_Opportunities → Dim_Date (CreatedDate) [inactive, use USERELATIONSHIP]
Fact_Activities → Fact_Opportunities (OpportunityID)
Fact_Activities → Dim_Owner (OwnerID)
Fact_Quotas → Dim_Owner (OwnerID)
Fact_Quotas → Dim_Date (PeriodID)

---

15 Essential Sales KPIs with DAX Formulas

1. Total Revenue (Won Deals)

Total Revenue =
CALCULATE(
    SUM(Fact_Opportunities[Amount]),
    Dim_Stage[IsClosedWon] = TRUE
)

2. Revenue vs Quota

Quota Attainment % =
DIVIDE(
    [Total Revenue],
    SUM(Fact_Quotas[QuotaAmount]),
    0
)

3. Pipeline Value (Open Deals)

Pipeline Value =
CALCULATE(
    SUM(Fact_Opportunities[Amount]),
    Dim_Stage[IsClosedWon] = FALSE,
    Dim_Stage[IsClosedLost] = FALSE
)

4. Weighted Pipeline (Probability-Adjusted)

Weighted Pipeline =
CALCULATE(
    SUMX(
        Fact_Opportunities,
        Fact_Opportunities[Amount] * Fact_Opportunities[Probability] / 100
    ),
    Dim_Stage[IsClosedWon] = FALSE,
    Dim_Stage[IsClosedLost] = FALSE
)

5. Win Rate

Win Rate =
DIVIDE(
    CALCULATE(COUNTROWS(Fact_Opportunities), Dim_Stage[IsClosedWon] = TRUE),
    CALCULATE(
        COUNTROWS(Fact_Opportunities),
        Dim_Stage[IsClosedWon] = TRUE || Dim_Stage[IsClosedLost] = TRUE
    ),
    0
)

6. Average Deal Size

Avg Deal Size =
CALCULATE(
    AVERAGEX(
        FILTER(Fact_Opportunities, Dim_Stage[IsClosedWon] = TRUE),
        Fact_Opportunities[Amount]
    )
)

7. Sales Cycle Length (Days)

Avg Sales Cycle Days =
AVERAGEX(
    FILTER(Fact_Opportunities, Dim_Stage[IsClosedWon] = TRUE),
    DATEDIFF(
        Fact_Opportunities[CreatedDate],
        Fact_Opportunities[CloseDate],
        DAY
    )
)

8. Year-over-Year Revenue Growth

Revenue YoY Growth =
VAR CurrentPeriod = [Total Revenue]
VAR PriorPeriod = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Dim_Date[Date]))
RETURN
DIVIDE(CurrentPeriod - PriorPeriod, PriorPeriod, 0)

9. Month-to-Date Revenue

MTD Revenue = CALCULATE([Total Revenue], DATESMTD(Dim_Date[Date]))

10. Quarter-to-Date Revenue

QTD Revenue = CALCULATE([Total Revenue], DATESQTD(Dim_Date[Date]))

11. Rolling 30-Day Revenue

Rolling 30D Revenue =
CALCULATE(
    [Total Revenue],
    DATESINPERIOD(Dim_Date[Date], LASTDATE(Dim_Date[Date]), -30, DAY)
)

12. Pipeline Coverage Ratio

Pipeline Coverage =
DIVIDE(
    [Pipeline Value],
    CALCULATE(
        SUM(Fact_Quotas[QuotaAmount]),
        DATESINPERIOD(Dim_Date[Date], LASTDATE(Dim_Date[Date]), 90, DAY)
    ),
    0
)

13. Deal Velocity (Revenue per Day)

Deal Velocity =
DIVIDE([Total Revenue], COUNTROWS(VALUES(Dim_Date[Date])), 0)

14. Average Activities per Won Deal

Avg Activities per Won Deal =
AVERAGEX(
    FILTER(Fact_Opportunities, Dim_Stage[IsClosedWon] = TRUE),
    CALCULATE(COUNTROWS(Fact_Activities))
)

15. Forecast Accuracy

Forecast Accuracy % =
1 - ABS(
    DIVIDE(
        [Total Revenue] - [Weighted Pipeline At Period Start],
        [Weighted Pipeline At Period Start],
        0
    )
)

---

4-Page Dashboard Architecture

Page 1: Executive Summary

Layout (top to bottom):

Row 1 — KPI Cards (5 cards across):

• Revenue MTD vs Quota (with quota attainment % and trend sparkline)
• Revenue YTD (with YoY % change indicator)
• Pipeline Value (with pipeline coverage ratio)
• Win Rate (with prior month comparison)
• Avg Deal Size (with trend indicator)

Row 2 — Primary Visuals (2 side by side):

• Left: Monthly Revenue vs Quota (clustered bar chart, last 12 months)
• Right: Revenue by Region (filled map or bar chart)

Row 3 — Secondary Visuals (3 side by side):

• Pipeline by Stage (funnel chart)
• Top 10 Deals by Amount (table with probability indicator)
• Revenue by Product Category (treemap)

Slicers (right panel or top): Date Range, Region, Sales Rep (multi-select)

Page 2: Pipeline Analysis

Layout:

Funnel Chart (stage-by-stage deal count and value):

• Prospecting → Qualified → Proposal → Negotiation → Closed Won

Risk Heatmap (conditional formatting table):

• Rows: Deals closing in next 30 days
• Columns: Deal Name, Account, Amount, Stage, Days in Stage, Owner
• Red highlight: Days in Stage > 30 (at risk of stalling)
• Amber: Days in Stage 15-30
• Green: Days in Stage < 15

Pipeline by Close Month (stacked bar):

• Bars by stage; shows how much pipeline closes when

Page 3: Sales Rep Performance

Matrix Visual (rep leaderboard):

• Rows: Sales Rep names
• Columns: Revenue, Quota, Attainment %, Pipeline, Win Rate, Avg Deal Size, Deals Won
• Conditional formatting: Red/Amber/Green on Attainment %
• Sort by Attainment % descending

Scatter Chart — Deal Size vs Win Rate per rep (identifies strengths):

• X axis: Average Deal Size
• Y axis: Win Rate
• Size: Total Revenue
• Color: Quota Attainment

Activity Analysis — Bar chart of calls/emails/meetings per rep

Page 4: Trends and Forecast

Line Chart — Rolling 12-month revenue with:

• Actual revenue (solid line)
• Forecast line (dashed, from weighted pipeline)
• Target line (flat, from quota)

Waterfall Chart — Revenue bridge from prior period:

• Starting: Last month revenue
• New Deals Won
• Expansions/upsells
• Losses
• Ending: Current month revenue

Cohort Analysis Table — New customers vs expansion revenue

---

Drill-Through Design

Drill-through allows managers to click a rep, region, or deal stage on summary pages and navigate to a detail page for that selection.

Configuring Drill-Through

1. Create a Detail Page in your report (e.g., "Deal Detail")
2. In the page's Drill Through area (Visualizations pane), add Fact_Opportunities[OpportunityID] as the drill-through field
3. Build the detail page: deal name, account, owner, stage history, activity log, notes

Users right-click any data point on summary pages and select "Drill through → Deal Detail" to see that specific deal's complete history.

Drill-Through for Rep Performance

Create a "Rep Detail" page with the rep name as drill-through field. Include:

• That rep's pipeline by stage
• Their deals closing this quarter (table)
• Activity trend over last 90 days
• Win rate vs team average

---

Conditional Formatting for At-Risk Deals

Apply conditional formatting to flag deals needing attention:

// Days in Current Stage (for conditional formatting)
Days in Stage =
DATEDIFF(
    CALCULATE(
        MAX(Stage_History[EnteredDate]),
        Stage_History[StageID] = MAX(Fact_Opportunities[StageID])
    ),
    TODAY(),
    DAY
)

// Deal Risk Color (background color measure)
Deal Risk Color =
SWITCH(TRUE(),
    [Days in Stage] > 30, "#FF4444",  -- Red: stalled
    [Days in Stage] > 15, "#FFA500",  -- Amber: warning
    "#00B050"                          -- Green: on track
)

Apply the color measure as background color in table conditional formatting rules.

---

Dynamic Targets and Quota Comparison

Loading quota data from a separate table (rather than hardcoding) allows targets to update without report republication:

Quota table structure:

Quota variance measure:

Revenue vs Quota =
[Total Revenue] -
CALCULATE(
    SUM(Fact_Quotas[QuotaAmount]),
    TREATAS(VALUES(Dim_Date[Year]), Fact_Quotas[Year]),
    TREATAS(VALUES(Dim_Date[MonthNum]), Fact_Quotas[Month])
)

---

Frequently Asked Questions

What data sources work best for Power BI sales dashboards?

CRM systems (Salesforce, Dynamics 365, HubSpot, Pipedrive) are the primary data sources for sales dashboards. Power BI connects natively to Dynamics 365 (via Dataverse) and Salesforce (via Salesforce connector). For HubSpot and Pipedrive, use the respective Power BI connectors or extract via API. Combine CRM data with ERP order data for a complete picture from opportunity to invoice.

How do I handle multiple currencies in a sales dashboard?

Create an exchange rate table with daily rates for each currency pair. Add a currency selector slicer to the report. Create a normalized measure that converts all amounts to a base currency using LOOKUPVALUE against the exchange rate table. For reporting with historical accuracy, use period-average rates (month-end or transaction-date rates) rather than today's spot rate.

Can managers see only their team's data automatically?

Yes — configure Row-Level Security with a hierarchy role. Create a role that uses USERPRINCIPALNAME() to look up the current user in the Dim_Owner table, then filter to show only their own deals and their direct/indirect reports' deals using PATH and PATHCONTAINS functions. Assign managers to this role in Power BI Service workspace settings.

How often should the sales dashboard refresh?

For active sales teams, hourly refresh (Power BI Premium or PPU) keeps data current enough for daily operations. For executive summary dashboards, 4-8 refreshes per day (Pro tier) is typically sufficient. If your CRM updates in real-time and you need true real-time visibility, configure streaming datasets for specific KPIs (deals closed today, pipeline changes) on top of the standard import dataset.

What is the best chart type for showing pipeline stage breakdown?

A funnel chart is the most intuitive for pipeline stage breakdown — it visually communicates conversion loss from stage to stage. Combine the funnel with a table below it showing count and value at each stage, plus average time in stage. For deal velocity analysis (how fast deals move through stages), use a bar chart showing average days per stage.

---

Next Steps

A Power BI sales dashboard built on a solid data model with the right KPIs transforms how sales leadership manages performance — from reactive (reviewing last month's data in Excel) to proactive (spotting at-risk deals and coaching opportunities in real time).

ECOSIRE's Power BI dashboard development team builds custom sales dashboards connected to your CRM and ERP systems. We design the data model, write optimized DAX measures, and build interactive reports your sales team will actually use.

Explore our Power BI dashboard development services or contact our team to discuss your sales analytics requirements and get a scoping estimate.