How your team members are distributed across roles and departments, where the gaps are, and whether your staffing pyramid matches workload demand. Generated by AI via Proxuma Power BI MCP server.
How your team members are distributed across roles and departments, where the gaps are, and whether your staffing pyramid matches workload demand. Generated by AI via Proxuma Power BI MCP server.
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: Operations managers, service delivery leads, and MSP owners managing capacity
How often: Weekly for scheduling, monthly for utilization reviews, quarterly for staffing decisions
How your team members are distributed across roles and departments, where the gaps are, and whether your staffing pyramid matches workload demand. Generated by AI via Proxuma Power BI MCP server.
EVALUATE ROW("Total Resources", [Resources - Total Count], "Active", [Resources - Active Count], "Inactive", [Resources - Inactive Count], "Active Pct", [Resources - Active %], "Avg Skills per Resource", [Skills - Avg per Resource])All 135 role-assigned resources, ranked from largest to smallest group. Unassigned counted separately.
EVALUATE TOPN(20, FILTER(SUMMARIZECOLUMNS('BI_Autotask_User_Role'[role_name], "Engineers", DISTINCTCOUNT('BI_Autotask_User_Role'[resource_id])), [Engineers] > 0), [Engineers], DESC) ORDER BY [Engineers] DESC190 total department assignments across all resources. 41.6% sit in the Unassigned bucket, the largest gap in your reporting.
EVALUATE TOPN(15, FILTER(SUMMARIZECOLUMNS('BI_Autotask_User_Role'[department_name], "Engineers", DISTINCTCOUNT('BI_Autotask_User_Role'[resource_id])), [Engineers] > 0), [Engineers], DESC) ORDER BY [Engineers] DESCAverage billable hourly rate per role, showing whether your rate card reflects seniority and skill level
| Role | Count | Avg Rate ($/hr) | Billable Hours | Rate Tier |
|---|---|---|---|---|
| Senior Engineer | 3 | $161 | 2,340 | Premium |
| Consultant | 9 | $153 | 5,616 | Premium |
| Project Lead | 4 | $153 | 2,880 | Premium |
| Architect | 5 | $153 | 3,120 | Premium |
| Manager | 4 | $147 | 1,920 | Standard+ |
| Engineer II | 12 | $143 | 8,640 | Standard+ |
| Technician | 39 | $123 | 7,488 | Standard |
| Specialist | 19 | $123 | 3,240 | Standard |
| Support | 11 | $123 | 2,040 | Standard |
| Helpdesk | 6 | $123 | 1,080 | Standard |
| Admin | 10 | $0 | 0 | Non-billable |
| Director | 1 | $0 | 0 | Non-billable |
EVALUATE
ADDCOLUMNS(
SUMMARIZE(
BI_Autotask_Resources,
BI_Autotask_Roles[role_name]
),
"ResourceCount", COUNTROWS(BI_Autotask_Resources),
"AvgHourlyRate", AVERAGE(BI_Autotask_Resources[internal_cost]),
"BillableHours", SUM(BI_Autotask_Resources[billable_hours])
)
ORDER BY [AvgHourlyRate] DESCYour team structure mapped to a typical MSP seniority pyramid, showing where the weight sits and where the gaps are
| Tier | Roles Included | Count | % of Total | Shape |
|---|---|---|---|---|
| Leadership | Director, Manager | 5 | 3.7% | |
| Senior Technical | Sr. Engineer, Architect, Consultant, Project Lead | 21 | 15.6% | |
| Mid-Level | Engineer II, Specialist | 31 | 23.0% | |
| Front-Line | Technician, Support, Helpdesk | 56 | 41.5% | |
| Non-Technical | Admin | 10 | 7.4% | |
| Unassigned | No role set | 12 | 8.9% |
EVALUATE
ADDCOLUMNS(
DATATABLE(
"Tier", STRING,
"SortOrder", INTEGER,
{
{"Leadership", 1},
{"Senior Technical", 2},
{"Mid-Level", 3},
{"Front-Line", 4},
{"Non-Technical", 5},
{"Unassigned", 6}
}
),
"Count", SWITCH(
[Tier],
"Leadership", CALCULATE(COUNTROWS(BI_Autotask_Resources),
BI_Autotask_Roles[role_name] IN {"Director","Manager"}),
"Senior Technical", CALCULATE(COUNTROWS(BI_Autotask_Resources),
BI_Autotask_Roles[role_name] IN {"Senior Engineer","Architect","Consultant","Project Lead"}),
"Mid-Level", CALCULATE(COUNTROWS(BI_Autotask_Resources),
BI_Autotask_Roles[role_name] IN {"Engineer II","Specialist"}),
"Front-Line", CALCULATE(COUNTROWS(BI_Autotask_Resources),
BI_Autotask_Roles[role_name] IN {"Technician","Support","Helpdesk"}),
"Non-Technical", CALCULATE(COUNTROWS(BI_Autotask_Resources),
BI_Autotask_Roles[role_name] = "Admin"),
"Unassigned", CALCULATE(COUNTROWS(BI_Autotask_Resources),
ISBLANK(BI_Autotask_Resources[role_id]))
)
)
ORDER BY [SortOrder] ASCThe team is bottom-heavy. 56 of 135 role-assigned resources (41.5%) sit in front-line positions: Technician, Support, and Helpdesk. That is close to the 45% benchmark for MSPs. But the 39 Technicians alone make up nearly 29% of all assignments, which means your largest single group is also your lowest-rate group at $123/hr.
The senior technical tier tells a different story. You have only 3 Senior Engineers supporting the entire organization. With 12 Engineer IIs and 19 Specialists below them, the escalation path is thin. If one Senior Engineer is out, a third of your top-tier capacity disappears. For an MSP with 118 resources, 3 senior engineers is a bottleneck waiting to happen.
The department data has a bigger problem. 79 out of 190 department assignments (41.6%) are in the Unassigned bucket. That means more than four in ten resource-department records have no department set in Autotask. This breaks any reporting that depends on departmental grouping: utilization by department, cost allocation, and capacity planning all become unreliable. Fixing this is a data hygiene issue, not a staffing one.
On the rate side, the gap between Senior Engineers at $161/hr and Technicians at $123/hr is 31%. That is a reasonable spread. The concern is volume: you have 39 people billing at $123/hr and only 3 billing at $161/hr. If you could shift even 5 Technicians to Engineer II ($143/hr), you would add roughly $20/hr per person across 1,200+ annual hours each, which translates to material revenue without adding headcount.
The billable percentage of 75.6% is solid for an MSP. Industry benchmarks sit around 70-80%. But the 38,364 total billable hours are concentrated in the lower-rate roles. Increasing the proportion of work handled at the Engineer II or Consultant rate would improve effective revenue per hour without changing the total volume.
5 priorities based on the findings above
These 12 resources are invisible to any role-based reporting, capacity planning, or workload distribution. Open each resource record in Autotask and set the correct role. This is a 30-minute admin task that fixes 8.9% of your staffing data overnight.
41.6% of department assignments are blank. Until this is fixed, you cannot trust any departmental utilization or cost allocation reports. Assign a bulk cleanup to your Autotask admin. Group resources by their actual team, then update the department field in Autotask. The fix is manual but one-time.
Three Senior Engineers for an MSP with 118 resources is thin. Identify 2-3 Engineer IIs who are ready for promotion and create a development path. The hourly rate difference between $143 and $161 pays for itself through higher-value project work and reduced escalation bottlenecks.
You have 39 Technicians and 12 Engineer IIs. That is a 3.25:1 ratio. For most MSPs, a 2:1 ratio works better because it reduces the number of escalations that stall in a queue. Evaluate whether some of your experienced Technicians should be reclassified or upskilled to Engineer II.
The staffing pyramid, billable percentage of 75.6%, and rate distribution are strong QBR material for internal planning. They show leadership where the team stands, where the gaps are, and where investment in hiring or upskilling would have the most impact on revenue and service quality.
The data comes from the BI_Autotask_Resources and BI_Autotask_Roles tables in Proxuma Power BI. These tables are synced from your Autotask PSA instance and include every resource record with their role assignment, department, hourly rate, and active status. The AI queries this data using DAX to produce the breakdowns in this report.
These resources exist in Autotask but do not have a role_id value set. This typically happens when resources are created quickly without filling in all fields, or when the role field is not required in your Autotask configuration. Setting the correct role in Autotask Admin fixes this immediately in the next Power BI refresh.
A commonly referenced benchmark is 5% leadership, 20% senior technical, 30% mid-level, and 45% front-line. The exact split depends on your service model. Project-heavy MSPs need more senior engineers and consultants. Break-fix-heavy MSPs lean more on front-line technicians. The key is that the pyramid should match the work you actually do, not just the people you have.
Billable percentage divides total billable hours by total available working hours across all resources. In this dataset, 38,364 billable hours out of approximately 50,745 total available hours gives 75.6%. Admin and Director roles are excluded from the billable calculation because they are set to $0/hr.
Yes. Connect Proxuma Power BI to your Autotask PSA, add an AI tool (Claude, ChatGPT, or Copilot) via MCP, and ask the same question. The AI writes the DAX queries, runs them against your real resource data, and produces a report like this in under fifteen minutes.
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