The data for this dashboard is visualised in powerbi showing the sales report for tech company
A sales pipeline report answers a different question depending on who is looking at it. A manager wants to know which agents are converting. A regional lead wants to know which territory is carrying the number. A product owner wants to know what is actually being bought. This report gives each of them a page rather than trying to serve all three from one screen.
Five pages: Agent Performance, Sales Team Performance, Product Performance, Sales by Location, and a Revenue Tooltip page that supplies hover detail to the others.
A star schema over a CRM pipeline extract of roughly 8,800 opportunities.
| Table | Grain and role |
|---|---|
| f_sales_pipeline | One row per opportunity. Carries deal_stage (Won, Lost, Engaging, Prospecting), engage_date, close_date, close_value, and keys to account, product and agent. |
| d_sales_teams | 35 agents, each with a manager and a regional_office. This is what makes the team page possible without hard-coding hierarchy into visuals. |
| d_accounts | 85 accounts with sector, employees, revenue and office_location. |
| d_products | 7 products grouped into series, with a list sales_price to compare against realised close_value. |
| d_date | Marked date table. Two relationships to the fact: one on engage_date, one on close_date. |
| All Measures / Top Bottom | Disconnected tables. The first holds every measure in one place; the second drives the top and bottom N selector on the ranking visual. |
The two date relationships are the interesting part. A pipeline has two clocks: when a deal was opened and when it closed. Revenue belongs to the close date, but pipeline creation belongs to the engage date. The report exposes both through the "Date open" and "Date close" buttons, and the model handles it with one active relationship and one inactive relationship activated on demand rather than a second date table.
Column names below are taken from the model. These are the measure definitions as the model computes them, not a paste of the formula bar.
Total Sales Revenue = CALCULATE( SUM( f_sales_pipeline[close_value] ), f_sales_pipeline[deal_stage] = "Won" ) No. of Deals Closed = CALCULATE( COUNTROWS( f_sales_pipeline ), f_sales_pipeline[deal_stage] = "Won" ) Average Deal Size = DIVIDE( [Total Sales Revenue], [No. of Deals Closed] ) Total Number of Clients = DISTINCTCOUNT( f_sales_pipeline[account] )
The definition that matters: won divided by concluded deals, not by everything in the pipeline. Counting open opportunities in the denominator makes every team look worse than it is, and makes the number move whenever pipeline is created rather than when it converts.
Win Rate =
VAR Won = CALCULATE( COUNTROWS( f_sales_pipeline ), f_sales_pipeline[deal_stage] = "Won" )
VAR Resolved =
CALCULATE(
COUNTROWS( f_sales_pipeline ),
f_sales_pipeline[deal_stage] IN { "Won", "Lost" }
)
RETURN DIVIDE( Won, Resolved )
The gap between engaging and closing, averaged over closed deals only. This is the measure that needs the two date columns rather than the date table.
Average Sales Cycle Length =
AVERAGEX(
FILTER( f_sales_pipeline, f_sales_pipeline[deal_stage] = "Won" ),
DATEDIFF( f_sales_pipeline[engage_date], f_sales_pipeline[close_date], DAY )
)
Revenue Month Increment $ = VAR Previous = CALCULATE( [Total Sales Revenue], DATEADD( d_date[Date], -1, MONTH ) ) RETURN [Total Sales Revenue] - Previous Revenue Month Increment % = VAR Previous = CALCULATE( [Total Sales Revenue], DATEADD( d_date[Date], -1, MONTH ) ) RETURN DIVIDE( [Total Sales Revenue] - Previous, Previous ) Quarterly Sales Growth = VAR Previous = CALCULATE( [Total Sales Revenue], DATEADD( d_date[Date], -1, QUARTER ) ) RETURN DIVIDE( [Total Sales Revenue] - Previous, Previous )
Percentage of Total Sales by Region =
DIVIDE(
[Total Sales Revenue],
CALCULATE( [Total Sales Revenue], REMOVEFILTERS( d_sales_teams[regional_office] ) )
)
Revenue Top 1 Product Name =
CONCATENATEX(
TOPN( 1, VALUES( d_products[product] ), [Total Sales Revenue], DESC ),
d_products[product]
)
Top and bottom agents on one chart, driven by a disconnected Top Bottom table rather than two visuals with opposite filters. The user picks the direction, the measure decides what to show.
Top Rank = RANKX( ALLSELECTED( d_sales_teams[sales_agent] ), [Total Sales Revenue],, DESC )
Bottom Rank = RANKX( ALLSELECTED( d_sales_teams[sales_agent] ), [Total Sales Revenue],, ASC )
Condition for Top & Bottom Agents =
VAR Selection = SELECTEDVALUE( 'Top Bottom'[Selection], "Top" )
VAR N = SELECTEDVALUE( 'Top Bottom'[N], 5 )
RETURN
IF(
SWITCH( Selection, "Top", [Top Rank], "Bottom", [Bottom Rank] ) <= N,
[Total Sales Revenue]
)
Revenue, win rate and deals closed as cards, a combo chart putting revenue bars against a win rate line, and the top and bottom ranking bar chart. The combo chart is the one that earns its place: an agent can be top for revenue and poor for conversion, and seeing both on one axis pair makes that visible immediately rather than requiring two charts and a mental join.
The same questions one level up. Win rate by team as a pie, revenue by region as a matrix, then average deal size and deals closed by region as paired column charts. Percentage of Total Sales by Region does the work here, because absolute revenue by region mostly tells you where the biggest region is.
Revenue and win rate by product, deals closed by product, and a scatter that puts volume against value so the cheap-and-frequent products separate visually from the expensive-and-rare ones. This is where the list price in d_products earns its place: comparing sales_price to realised close_value shows where discounting is happening.
Revenue mapped by office location, using a custom map visual rather than the built-in one for better control over bubble scaling.
A hidden page that renders as a hover tooltip on the other pages, showing revenue by product for whatever is under the cursor. Detail on demand rather than another visual competing for space.
The obvious gap is that this measures outcomes rather than the pipeline itself. Deals sitting in Engaging or Prospecting only appear as the absence of revenue, so there is no view of pipeline coverage against target, and no ageing of open opportunities. Adding a stage-weighted pipeline value and a days-in-stage measure would turn it from a scoreboard into something a manager could act on mid-quarter. Beyond that, sector and employee count sit unused in d_accounts, which is a ready-made segmentation of which company profiles actually convert.