Calculated, Rollup, and Formula Columns

Columns that work out their own value

Nadia has three requirements on the shipment table.

1. Total charge = base rate plus fuel surcharge, on the same row.
2. Number of parcels on this shipment, counted from the related parcel rows.
3. The same total as in requirement 1, but written in Power Fx like the rest of her app.

Three requirements, three different column types. Getting this family straight is one of the highest-value things you can do for this exam, because the differences are precise and the wrong choice produces a column that quietly does nothing useful.

The three self-calculating column types

Column type	Computes from	When it computes
Calculated	Columns on the same row, or a parent row	In real time, when the value is retrieved
Formula	Columns on the same row, or a related row, using Power Fx	In real time, when the value is retrieved
Rollup	Aggregating many related rows	Asynchronously, by a background system job

Timing is the first fork in the road

Formula and calculated columns are calculated in real time when they are retrieved. Read the row, get a current value.

Rollup columns are not. They are produced by scheduled system jobs that run asynchronously in the background. That is not a defect — aggregating across thousands of related rows on every read would be ruinous — but it changes what you can build on top of them.

The two rollup jobs

• Mass Calculate Rollup Field — one per rollup column. Runs once after you create or update the column and recalculates every existing row. By default it runs 12 hours after the change, deliberately, so it lands in quiet hours. Administrators are advised to move the start time to something like midnight.
• Calculate Rollup Field — one per table, doing incremental calculations for rows created, updated, or deleted since the mass job finished. Default minimum recurrence is one hour. It is created automatically when the first rollup column on a table is created and deleted when the last one is removed.

Both are visible under Settings → Advanced settings → System Jobs → Recurring System Jobs, and you need administrator rights to manage them.

💡 The Recalculate button

On a form, a rollup column shows a calculator icon, the value, and the time it was last calculated. Select the icon and a Recalculate button appears for an on-demand refresh.

Two conditions apply. You need write privileges on the table and write access on the source row — notably not on the related table being aggregated. And it works online only; it is unavailable offline.

Rollup columns: what they aggregate

Rollups use SUM, COUNT, MIN, MAX, and AVG, with full filter support on the source or related table, and they can aggregate over a hierarchy of rows. They are solution components, so they move between environments like anything else.

Now the restrictions, which is where the exam lives.

⚠️ Rollup restrictions worth memorising

• 1:N only. A rollup formula cannot include rows in a many-to-many (N:N) relationship.
• No rollup over a rollup. A rollup formula cannot reference another rollup column.
• Simple calculated or formula columns only — ones referencing simple columns on the same row. Complex ones are not allowed as rollup sources.
• Rollups do not trigger workflows and cannot be used as a workflow wait condition. They do not raise the event.
• ModifiedBy and ModifiedOn are not updated when a rollup value changes.
• 1:N relationships with the ActivityPointer and ActivityParty tables cannot be used.
• A rollup that depends on a time-bound formula column — one using Now() or IsUTCToday() — does not update automatically. It needs an online recalculation.
• There is a configurable per-environment and per-table limit on how many rollup columns you can create.

Question

A requirement says: when the total shipment weight rollup exceeds 500 kg, start an approval. What is wrong with this design?

Click or press Enter to reveal answer

Answer

Rollup columns do not raise the event that triggers workflows, so nothing will start when the rollup value changes. The aggregate also lags behind reality because it is produced by an asynchronous job. Trigger the automation from the underlying parcel rows instead, or run a scheduled flow that reads the value.

Click to flip back

That flashcard is the single most common rollup mistake in real projects. A rollup is for display and reporting, not for driving automation.

Formula columns: Power Fx in the data layer

Formula columns bring Power Fx — the same language as canvas apps — into column definitions. Same real-time behaviour as calculated columns, more familiar syntax, and the ability to reference related-row columns.

Formula column validations

• A formula column may reference other formula columns, but not itself.
• No cyclic chains. F1 = F2 + 10 together with F2 = F1 * 2 is invalid.
• Maximum expression length 1,000 characters.
• Maximum depth 10 — depth counts the chain of formula columns referring to other formula or rollup columns.
• Currency needs care. Direct use of currency and exchange-rate columns is unsupported; wrap with Decimal(...). Base currency columns are not supported at all, and neither are currency columns from related tables.
• In model-driven apps, sorting is disabled on a formula column that contains a related-table column or a logical column.
• Formula columns have no value when a mobile client user is offline.

The null trap — the best question in this whole area

Formula and calculated columns disagree about what happens when a number is empty, and the difference is exactly the kind of detail exams are built on.

Null arithmetic differs between formula and calculated columns

Expression a + b + c where a is null, b is 2, c is 3	Formula column	Calculated column
Intermediate handling of null	Null is treated as 0	Null stays null
Result	5	null

Question

A total column returns blank whenever the surcharge field is empty. It was built as a calculated column. What single change fixes it?

Click or press Enter to reveal answer

Answer

Rebuild it as a formula column. In a formula column a null numeric value is treated as 0 in the intermediate operation, so the total still computes. In a calculated column the null propagates and the whole expression returns null.

Click to flip back

One more difference: the plug-in pipeline

Only calculated column values are available in the retrieve plug-in pipeline. Formula column values are not.

That sounds like developer trivia, and mostly it is — but it is the one scenario where a calculated column is genuinely the correct answer over a formula column, so it is worth holding on to.

Choosing between the three

Matching the requirement to the column type

Requirement	Column type	Why
Base rate plus surcharge on the same row	Formula	Same-row arithmetic, and nulls will not blank the result
Count of parcels on a shipment	Rollup	Aggregating many related rows is what rollups do
Highest declared value across a shipment's parcels	Rollup with MAX	An aggregate function over related rows
Value must be exposed to a retrieve plug-in	Calculated	Only calculated column values reach that pipeline
Value must be visible to an offline mobile user	Not a formula or calculated column	Microsoft states formula and calculated columns have no value offline. Rollup values are persisted in the database, but the docs do not settle their offline visibility — do not assume either way
Automation must run when the total changes	None of the three alone	Rollups do not raise workflow events; trigger from the source rows
Aggregate across an N:N relationship	Not a rollup	Rollup formulas cannot include N:N rows

💡 A reliable way to decide under exam pressure

Ask two questions in order.

1. Is it aggregating many related rows? If yes, it is a rollup — then immediately check for the disqualifiers: N:N, rollup-over-rollup, needing to trigger automation.
2. If not, does anything in the scenario mention nulls, Power Fx, or related-row columns? That points to a formula column. A retrieve plug-in points to a calculated column.

Quick check

Knowledge Check

Kite Freight needs a column on the shipment row showing the total declared value of all related parcel rows. Parcels relate to shipments through a one-to-many relationship. Which column type?

AA rollup column using SUMBA formula column using Sum() across the relationshipCA calculated column referencing the parcel tableDA business rule that sets the value on save

Check Answer

Knowledge Check

A maker reports that a shipment total shows as blank whenever the optional surcharge column is empty. The total is a calculated column adding three numeric columns. What explains this?

AIn a calculated column a null value propagates, so the whole expression returns nullBCalculated columns cannot add more than two columnsCThe calculated column has not been recalculated by its system job yetDOptional columns are excluded from calculated column expressions

Check Answer

Knowledge Check

An administrator wants a cloud flow to start whenever a rollup column's value changes. What should they be told?

ARollup columns do not raise the event that triggers workflows, so the flow should be triggered from the underlying related rows insteadBThe flow must use the Mass Calculate Rollup Field job as its triggerCRollup columns can trigger flows only when the Recalculate button is usedDThis works as long as the flow trigger scope is set to Organization

Check Answer

What to carry forward

• Formula and calculated compute in real time on retrieve. Rollup is asynchronous.
• Rollup aggregates with SUM, COUNT, MIN, MAX, AVG over 1:N relationships only.
• Rollups: no N:N, no rollup over a rollup, no workflow triggering, no ModifiedOn update.
• Two jobs: Mass Calculate Rollup Field per column (~12 hours after a change) and Calculate Rollup Field per table (minimum one hour).
• Recalculate on the form needs write access to the source row and works online only.
• The null trap: formula gives 5, calculated gives null.
• Only calculated column values reach the retrieve plug-in pipeline.
• Formula columns: Power Fx, no self-reference, no cycles, 1,000 characters, depth 10, awkward with currency, blank offline.

That completes the technical ground. The final module pulls the whole exam together.