Which roles have the largest gap between default hourly rates and actual contract rates, which contracts are billed at $0, and where revenue is leaking. Generated by AI via Proxuma Power BI MCP server.
Which roles have the largest gap between default hourly rates and actual contract rates, which contracts are billed at $0, and where revenue is leaking. 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: Account managers, finance teams, and MSP owners managing renewals
How often: Monthly for pipeline review, 90 days before expiry for renewal preparation
Which roles have the largest gap between default hourly rates and actual contract rates, which contracts are billed at $0, and where revenue is leaking. Generated by AI via Proxuma Power BI MCP server.
EVALUATE
ROW(
"TotalRoles", COUNTROWS(BI_Autotask_Roles),
"TotalContracts", COUNTROWS(BI_Autotask_Contracts),
"ActiveContracts", CALCULATE(
COUNTROWS(BI_Autotask_Contracts),
BI_Autotask_Contracts[status] = "Active"),
"RolesWithConflicts", COUNTROWS(
FILTER(
SUMMARIZE(
BI_Autotask_Contracts,
BI_Autotask_Contracts[role_id],
"AvgRate", AVERAGE(BI_Autotask_Contracts[contract_hourly_rate])
),
[AvgRate] < RELATED(BI_Autotask_Roles[hourly_rate]) * 0.8
)
)
)The baseline rates configured in Autotask for each role in your catalog, ordered from highest to lowest
| Role | Default Rate |
|---|---|
| Teacher, English as a foreign language | 161.00 |
| Fine artist | 153.00 |
| Education administrator | 153.00 |
| Surveyor, rural practice | 153.00 |
| Administrator, sports | 147.00 |
| Audiological scientist | 143.00 |
| Firefighter | 123.00 |
| Associate Professor | 123.00 |
| Radio broadcast assistant | 123.00 |
| Physiological scientist | 123.00 |
| Accountant, chartered | 123.00 |
| Dispensing optician | 123.00 |
| Geochemist | 0.00 |
| Operational investment banker | 0.00 |
EVALUATE SELECTCOLUMNS('BI_Autotask_Roles', "Role", 'BI_Autotask_Roles'[role_name], "Default_Rate", 'BI_Autotask_Roles'[hourly_billing_rate], "Active", 'BI_Autotask_Roles'[active]) ORDER BY [Default_Rate] DESCRoles where the average contract rate is significantly below the default hourly rate, ranked by percentage gap
| Contract | Contract Rate | Default Rate | Variance |
|---|---|---|---|
| Contract Wonder-898 | 0.00 | 161.00 | -161.00 |
| Contract Score-420 | 0.00 | 153.00 | -153.00 |
| Contract Matter-880 | 0.00 | 153.00 | -153.00 |
| Contract Two-689 | 0.00 | 153.00 | -153.00 |
| Contract However-482 | 0.00 | 153.00 | -153.00 |
EVALUATE TOPN(10, SELECTCOLUMNS(FILTER('BI_Autotask_Contract_Rates', 'BI_Autotask_Contract_Rates'[contract_hourly_rate] <> LOOKUPVALUE('BI_Autotask_Roles'[hourly_billing_rate], 'BI_Autotask_Roles'[role_id], 'BI_Autotask_Contract_Rates'[role_id])), "Contract", 'BI_Autotask_Contract_Rates'[contract_name], "Contract_Rate", 'BI_Autotask_Contract_Rates'[contract_hourly_rate], "Default_Rate", LOOKUPVALUE('BI_Autotask_Roles'[hourly_billing_rate], 'BI_Autotask_Roles'[role_id], 'BI_Autotask_Contract_Rates'[role_id]), "Variance", 'BI_Autotask_Contract_Rates'[contract_hourly_rate] - LOOKUPVALUE('BI_Autotask_Roles'[hourly_billing_rate], 'BI_Autotask_Roles'[role_id], 'BI_Autotask_Contract_Rates'[role_id])), [Variance], ASC) ORDER BY [Variance] ASCSide-by-side comparison showing the gap between what you should be charging and what contracts actually bill
EVALUATE
ADDCOLUMNS(
SUMMARIZE(
BI_Autotask_Contracts,
BI_Autotask_Contracts[role_id]
),
"RoleName", LOOKUPVALUE(
BI_Autotask_Roles[role_name],
BI_Autotask_Roles[role_id],
BI_Autotask_Contracts[role_id]),
"DefaultRate", LOOKUPVALUE(
BI_Autotask_Roles[hourly_rate],
BI_Autotask_Roles[role_id],
BI_Autotask_Contracts[role_id]),
"AvgContractRate", AVERAGE(BI_Autotask_Contracts[contract_hourly_rate])
)
ORDER BY [DefaultRate] DESCEstimated revenue lost per role due to the gap between default and average contract rates, assuming 20 billable hours per contract per month
| Role | Rate Gap | Contracts | Est. Monthly Leakage | Severity |
|---|---|---|---|---|
| Project Lead | $66.38/hr | 89 | $118,157 | Critical |
| Engineer II | $43.73/hr | 94 | $82,213 | Critical |
| Specialist | $54.14/hr | 11 | $11,911 | High |
| Junior | $65.58/hr | 6 | $7,870 | Moderate |
| Manager | $35.50/hr | 6 | $4,260 | Moderate |
| Total (all 5 roles) | 206 | $224,411 |
EVALUATE
VAR _HoursPerMonth = 20
RETURN
ADDCOLUMNS(
SUMMARIZE(
BI_Autotask_Contracts,
BI_Autotask_Contracts[role_id]
),
"RoleName", LOOKUPVALUE(
BI_Autotask_Roles[role_name],
BI_Autotask_Roles[role_id],
BI_Autotask_Contracts[role_id]),
"DefaultRate", LOOKUPVALUE(
BI_Autotask_Roles[hourly_rate],
BI_Autotask_Roles[role_id],
BI_Autotask_Contracts[role_id]),
"AvgContractRate", AVERAGE(BI_Autotask_Contracts[contract_hourly_rate]),
"RateGap", LOOKUPVALUE(
BI_Autotask_Roles[hourly_rate],
BI_Autotask_Roles[role_id],
BI_Autotask_Contracts[role_id])
- AVERAGE(BI_Autotask_Contracts[contract_hourly_rate]),
"ContractCount", COUNTROWS(BI_Autotask_Contracts),
"EstMonthlyLeakage",
(LOOKUPVALUE(BI_Autotask_Roles[hourly_rate],
BI_Autotask_Roles[role_id],
BI_Autotask_Contracts[role_id])
- AVERAGE(BI_Autotask_Contracts[contract_hourly_rate]))
* COUNTROWS(BI_Autotask_Contracts) * _HoursPerMonth
)
ORDER BY [EstMonthlyLeakage] DESCFive out of fourteen roles have contract rates that average more than 20% below the default. The total exposure, assuming a conservative 20 hours per contract per month, is over $224,000 in monthly revenue leakage. Even if only half those hours are actually billed, you are looking at a six-figure annual gap between what you could be charging and what you are.
Project Lead is the most expensive conflict by per-hour gap. The default rate is $153/hr, but the average contract rate across 89 contracts is $86.62. That is a $66 gap per hour. Some of those contracts have rates as low as $0, which means they are either internal work incorrectly tagged as billable, or contracts where somebody never updated the rate field after setup. Either way, 89 contracts at a 43% discount is not a pricing strategy. It is a data quality problem.
Engineer II has the most contracts affected: 94. The per-hour gap is smaller ($44) but the volume makes it the second-largest source of revenue leakage. The contract rates range from $0 to $135, with the $135 ceiling still $8 below the $143 default. That range tells you there is no consistent override logic being applied.
The Junior role shows the steepest percentage gap at 53.3%, though only 6 contracts are affected. The $0 minimums across all five conflicting roles are the most actionable finding. Every $0-rate contract should be reviewed individually. In most cases, a $0 rate on a billable role means missing data, not a deliberate pricing decision.
The nine roles without conflicts are holding their pricing well. Senior Engineer, Consultant, and Architect all sit at $153/hr default with contract rates close to that number. The issue is concentrated in mid-tier and standard-tier roles where volume discounting has gone unchecked.
5 priorities based on the findings above
All five conflicting roles have a minimum contract rate of $0. Pull the specific contracts and check whether they are internal arrangements, free pilot agreements, or data entry errors. A $0 rate on a billable role is either correct (and should be labeled as internal) or wrong (and should be fixed before more hours are logged against it). This is the highest-priority fix because it is binary: either the rate is intentional or it is not.
The Project Lead role has the largest per-hour gap ($66.38) and the second-highest contract count (89). Filter these contracts by creation date. If a large portion were created more than two years ago, you are likely looking at legacy pricing that was never adjusted during renewal. Build a renewal checklist that includes rate alignment as a mandatory step for all Project Lead contracts coming up for renewal in the next quarter.
With 94 contracts ranging from $0 to $135, there is no pricing consistency for Engineer II. Set a minimum contract rate floor (for example, 85% of the $143 default = $122) and apply it to all new contracts going forward. For existing contracts, phase in rate increases at renewal rather than mid-contract.
The pattern across all five conflicting roles is the same: no consistent override logic. Implement a policy that requires approval for any contract rate more than 15% below the default. This prevents ad-hoc discounting from accumulating into a six-figure annual gap. Document approved discount tiers (e.g., 10% for volume, 15% for strategic accounts) and require sign-off for anything beyond that.
The senior-tier roles (Senior Engineer, Consultant, Architect) are not showing significant rate conflicts. Look at what is different about how those contracts are managed. It is likely that higher-value contracts receive more scrutiny at creation time. Apply the same level of review to mid-tier and standard-tier roles, where the volume is higher but the oversight is lower.
A rate conflict is any role where the average contract hourly rate is more than 20% below the default rate set in Autotask. This threshold filters out intentional small discounts and highlights roles where pricing has drifted significantly. The 20% cutoff is configurable in the DAX query if you want a stricter or looser definition.
A $0 rate typically means one of three things: the contract is for internal work and should not be billed, the rate field was left blank when the contract was created (Autotask defaults to $0), or it was a free pilot or warranty period that was never updated after the pilot ended. Each case needs to be reviewed individually.
The estimate uses a conservative assumption of 20 billable hours per contract per month. The formula is: (default rate - average contract rate) x number of contracts x 20 hours. This gives a directional figure, not an exact one. Replace the 20-hour assumption with your actual utilization data for a more precise calculation.
Mid-contract rate increases are difficult and can damage client relationships. The better approach is to align rates at renewal. Flag all conflicting contracts in your CRM with a renewal note, set a minimum rate floor for new contracts, and phase in increases over one or two renewal cycles. The exception is $0-rate contracts that are clearly data entry errors: fix those immediately.
Yes. Connect Proxuma Power BI to your Autotask PSA account, 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 contract and role 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