Rollup vs calculated columns in Dataverse: the async trap we fell for

A deal desk dashboard showed the running total of opportunities per account. Total amount per account was a rollup column. Users opened the dashboard, saw a total, made a decision. Then someone added a new opportunity and checked the same account. The account's total did not change.

Fifteen minutes later, refresh - still not changed. An hour later - changed. The rollup column was working exactly as documented, and the team had mistaken it for a real-time aggregate.

Here is the difference between calculated and rollup columns, and the pattern we now use when the dashboard needs to be current.

The two column types

Calculated column: computes its value every time the row is read. If the column adds two other columns, every time anything loads the row (form view, API call, view row), Dataverse computes the sum. Results are always current because they are re-derived on demand.

Rollup column: computes its value on a schedule. The default schedule is every 12 hours, configurable down to one hour per column. A rollup that sums child record amounts reads the current state of children when the schedule fires, stores the result, and serves that stored value until the next schedule.

Both store their definition (the formula) in the solution. Both look identical to consuming code. The runtime behavior is radically different.

When each one is correct

Calculated columns are correct when:

• The formula depends only on fields on the same row, or on a parent's fields (via lookup)
• The computation is cheap (arithmetic, string concatenation, conditionals)
• You need the value to be current every time it is read

Rollup columns are correct when:

• The formula aggregates across many child records (SUM, COUNT, AVG, MIN, MAX)
• The underlying data changes frequently but the aggregate does not need to be current within seconds
• Scheduling async recomputation is acceptable

A calculated column that aggregates children is not allowed. Dataverse blocks it - the reason is performance, reading an account would trigger a query across all its opportunities every load. Rollup is the platform's answer: compute the aggregate ahead of time, serve it cached.

Why the async nature bites

Teams who want a rolling total of child records are usually asked for it by a business user who sees the number on a dashboard and expects it to match reality. When a user tells the team "the total on the Account form should show all linked Opportunity amounts," the team builds a rollup column. The dashboard shows the rollup.

The user then creates an opportunity, looks at the account, and the total is stale. From the user's perspective, the number is wrong. From the platform's perspective, the number is accurate for the last time the schedule fired.

The fix is either to lower the user's expectations (the dashboard updates on a schedule) or to change the implementation.

Real-time totals: three alternatives

When the aggregate genuinely needs to be current:

Alternative 1: plugin that updates a stored total on every child change.

A post-operation plugin on Opportunity (Create, Update on amount, Delete) recalculates acme_total_opportunities on the parent Account and writes it. Works for modest volumes - an account with 200 opportunities, each updating rarely, is fine. Fails for accounts with 20,000 opportunities updating frequently - every child change becomes a parent write, and the Dataverse execution pipeline starts throttling.

Good for: master-detail relationships with medium fanout.

Alternative 2: calculated column summing via the lookup.

You cannot aggregate children from a parent via a calculated column, but you can compute the child's contribution per row using a calculated column on the child. acme_attributable_amount = IF(acme_is_counted, amount, 0) on Opportunity, then use that in any consumer query. The consumer does the aggregation at read time.

Works for: dashboards that can run FetchXML with aggregate operators (SUM the acme_attributable_amount across children in a single query). Fails if the consumer is a Dataverse form or view that cannot run aggregate queries.

Good for: Power BI reports, custom dashboards, anywhere you control the read query.

Alternative 3: rollup with one-hour schedule plus "Refresh" button.

Keep the rollup for the baseline case. Add a button on the form that explicitly triggers a rollup recalculation via the CalculateRollupField SDK request. User experiences it as "I just added a child, now I hit Refresh, the total updates."

Works for: dashboards with bursty usage patterns - the baseline is acceptable, explicit refresh covers the "just changed it" case.

Good for: sales management screens, deal desk reviews, scenarios where users understand the refresh semantics.

The audit we run on rollups

On any project with more than three rollup columns, we audit quarterly:

1. Is the rollup still in use? Rollups accumulate - someone adds one for a requirement that later gets dropped, the column stays. Deprecated rollups still consume schedule slots.
2. Is the schedule frequency right? A rollup set to 12 hours when the underlying data changes every hour is both wrong (stale) and wasteful (the 12-hour window sees many state changes).
3. Is the formula still correct? Filters in rollup definitions reference specific statecode/statuscode values. When those values change semantics in later releases, rollups silently compute the wrong thing.

The audit takes 20 minutes per project. Half the time, at least one rollup can be deleted; a third of the time, at least one schedule needs adjusting.

Gotcha: deleting a child does not trigger rollup

A rollup of child-record amounts updates when a child is created or its amount changes. It does not update when a child is deleted, until the scheduled refresh fires.

The window between "child deleted" and "rollup refreshed" shows an overstated total. This is especially painful in audit scenarios - a user deletes a duplicate, the total still shows the duplicate's contribution for up to twelve hours.

The fix is either a plugin on the child Delete event (post-operation, trigger a rollup recalculation explicitly via CalculateRollupFieldRequest) or a tighter schedule. We usually go with the plugin when the table supports deletions, because the time-lag is user-visible.

The rule we enforce

Rollup columns ship with a form tooltip: "This total refreshes every N hours. Click Refresh after adding new records to see updated totals immediately."

The tooltip is one field-description line, it costs nothing to add, and it inoculates the "why is the number stale" ticket that would otherwise land every other week. Every rollup we ship has it. Every one we inherit gets it added on the first pass.