This report provides a detailed breakdown of crm activity dashboard: sales pipeline & opportunity tracking for managed service providers.
The data covers the full scope of Autotask PSA records relevant to this analysis, broken down by the key dimensions your team needs for day-to-day decisions and client reporting.
Who should use this: MSP operations teams and service delivery managers
How often: As needed for specific analysis or reporting requirements
-- Active opportunities count and total value
EVALUATE
ROW(
"Active_Opps",
CALCULATE(
COUNTROWS(BI_CRM_Opportunity),
BI_CRM_Opportunity[Status] = "Active"
),
"Pipeline_Value",
CALCULATE(
SUM(BI_CRM_Opportunity[Revenue]),
BI_CRM_Opportunity[Status] = "Active"
),
"Won_Count",
CALCULATE(
COUNTROWS(BI_CRM_Opportunity),
BI_CRM_Opportunity[Status] IN {"Closed", "Implemented"}
),
"Win_Rate_Pct",
DIVIDE(
CALCULATE(
COUNTROWS(BI_CRM_Opportunity),
BI_CRM_Opportunity[Status] IN {"Closed", "Implemented"}
),
CALCULATE(
COUNTROWS(BI_CRM_Opportunity),
BI_CRM_Opportunity[Status] IN {"Closed", "Implemented", "Lost"}
)
)
)| Type | Count | Tickets |
|---|---|---|
| Customer | 328 | 66546 |
| Vendor | 133 | 913 |
| Prospect | 77 | 1 |
| Cancellation | 11 | 61 |
| Lead | 1 | None |
EVALUATE SUMMARIZECOLUMNS(BI_Autotask_Companies[company_type], "Count", COUNTROWS(BI_Autotask_Companies), "AvgTickets", [Tickets - Count - Created]) ORDER BY [Count] DESC| Sales Rep | Total Deals | Total Revenue | Avg Deal Size | Volume Tier |
|---|---|---|---|---|
| Travis Peterson | 217 | €4,716,186 | €21,734 | High value |
| Michelle Chapman | 77 | €1,156,494 | €15,019 | Mid volume |
| Stephen Nelson | 287 | €903,330 | €3,148 | High volume |
| James Sparks | 160 | €569,060 | €3,557 | High volume |
| Paul Hoffman | 151 | €448,569 | €2,972 | High volume |
| Sean Castillo | 77 | €294,852 | €3,830 | Mid volume |
| Kristie Berry | 235 | €210,696 | €896 | Small deals |
-- Sales rep performance: deals, revenue, average deal size
EVALUATE
ADDCOLUMNS(
SUMMARIZE(
BI_CRM_Opportunity,
BI_CRM_Opportunity[OwnerName],
"Deal_Count", COUNTROWS(BI_CRM_Opportunity),
"Total_Revenue", SUM(BI_CRM_Opportunity[Revenue])
),
"Avg_Deal_Size",
DIVIDE([Total_Revenue], [Deal_Count])
)
ORDER BY [Total_Revenue] DESC-- Win/loss breakdown by outcome status with revenue
EVALUATE
ADDCOLUMNS(
SUMMARIZE(
BI_CRM_Opportunity,
BI_CRM_Opportunity[Status],
"Deal_Count", COUNTROWS(BI_CRM_Opportunity),
"Total_Revenue", SUM(BI_CRM_Opportunity[Revenue])
),
"Win_Rate",
IF(
BI_CRM_Opportunity[Status] IN {"Closed", "Implemented"},
DIVIDE(
CALCULATE(
COUNTROWS(BI_CRM_Opportunity),
BI_CRM_Opportunity[Status] IN {"Closed", "Implemented"}
),
CALCULATE(
COUNTROWS(BI_CRM_Opportunity),
BI_CRM_Opportunity[Status] IN {"Closed", "Implemented", "Lost"}
)
),
BLANK()
)
)
ORDER BY [Total_Revenue] DESCThe pipeline data reveals a concentration story: 77.7% of active opportunity value sits in Proposal Sent, meaning nearly €3.1M depends on 46 proposals that have gone out but not yet received a decision. This is a normal pattern for MSPs where proposals require review time, but it does mean the business should be tracking how long those proposals have been sitting and following up systematically.
The rep performance split is worth examining closely. Travis Peterson generates by far the highest revenue per deal at €21,734 average, handling enterprise-grade opportunities. Stephen Nelson has the most deals at 287 but a much lower average of €3,148, suggesting a volume-based approach with smaller clients. Neither approach is wrong, but understanding the mix helps forecast more accurately and allocate sales support where it matters most.
The loss picture also carries useful information. 509 lost deals represent €7.06M in missed revenue against €3.97M in won revenue from closed and implemented deals combined. This does not necessarily mean the win rate is bad, because high-value deals that go to competitors inflate the lost revenue figure. Tracking the reason for each loss in Autotask gives Power BI the data it needs to break this down further.
Having this data in Power BI means your service delivery team and leadership see the same numbers. When a big deal is close to signing, the ops team can plan capacity accordingly. When the pipeline drops, leadership spots it in the same dashboard view rather than waiting for a monthly CRM export.
Nearly €3.1M in value is waiting on a decision. Set a Power BI alert or weekly review cadence that flags any proposal over 14 days with no status change. That alone can recover deals that are simply sitting in someone's inbox.
18 "Lost/Stale" and 16 "Expired" deals are inflating your active opportunity count without real pipeline value behind them. Closing or archiving them gives you cleaner numbers and more accurate forecasting in the dashboard.
The contrast between high-volume low-value reps and low-volume high-value reps is visible in Power BI. Use this in monthly 1:1 reviews: where is each rep's time going, and is the deal mix aligned with your growth strategy?
Right now, €7.06M in lost revenue shows up as a single number. If reps capture a loss reason (price, timing, competition, scope mismatch) in every closed-lost opportunity, Power BI can break that down and show you where the real problem is.
Yes. Proxuma supports both Autotask and ConnectWise Manage as PSA sources. The CRM module in ConnectWise uses a slightly different data structure, so the opportunity stages and field names differ, but the Power BI dashboard logic is the same. Your account setup determines which connector is active.
By default, Proxuma refreshes the dataset once per day. For organizations that want near-real-time pipeline visibility, a more frequent refresh schedule can be configured. The refresh interval depends on your Power BI license and the data volume from your PSA. Most MSPs find a daily refresh sufficient for CRM review purposes.
The live CRM Activity dashboard in Power BI includes slicers for rep name, date range, opportunity stage, and deal status. Managers can filter to see a single rep's pipeline, and reps can filter to their own view. The filters persist within a session so you can drill down without losing context.
Yes, that is one of the core benefits of connecting your PSA to Power BI. Because both CRM data and service data come from the same system via the same connector, you can build combined views that show a client's open ticket count alongside their deal status, or compare billable hours delivered against contract value won. This context is not available in standard PSA reporting.
Connect Proxuma Power BI to your PSA, RMM, and M365 environment, use an MCP-compatible AI to ask questions, and generate custom reports - in minutes, not days.
See more reports Get started