How I got a subtotal to subtract one of its children — and still roll up correctly to the total — without touching the parent.
The problem
I had a P&L subtotal — I'll call it Net Other Income & Expense — in a standard list hierarchy. By default, Anaplan sums the children into the parent:
Net Other Income & Expense
├── Other Income .............. 40
├── Interest Expense .......... 100
└── Gain on Extinguishment ...... 0
Default sum = 140
But this subtotal shouldn't add Other Income — it should subtract it:
Net Other Income & Expense = Interest Expense + Gain on Extinguishment − Other Income
= 100 + 0 − 40
= 60 ← what I actually need
Two requirements made this tricky:
- Other Income must still display as a positive 40 on its own line — no negative signs shown to the business.
- The corrected 60 must cascade upward into Total, Profit Before Tax, and everything above — it's not just a cosmetic number on one line.
The catch: in Anaplan, a parent that uses Summary = Sum only adds its children — you can't inject custom math into it. So instead of fighting the parent, the trick is to flip the sign at the leaf (so the correct number cascades up naturally through Sum), then flip it back for display only.
Here's the step-by-step build.
Step 1 — Create a sign flag
In a small attributes module dimensioned by the account list only, add a boolean line item:
- Name:
Sign Flip for Net? - Format: Boolean
- Summary:
All ← this setting is the key (explained in Step 4)
Set it TRUE only on the leaves under Other Income — e.g. Dividend Income and Misc Income. Do not touch any parent cell (you can't anyway — parent cells in Anaplan are always derived, never editable).
Step 2 — Build the engine line item (this is what cascades)
In your module, add a new line item:
- Name:
Amount (Calc) - Summary:
Sum - Formula:
IF 'SYS_AccountAttributes'.'Sign Flip for Net?' THEN -Amount ELSE Amount
Because Summary is Sum, this formula runs at the leaf level — flipping the flagged accounts to negative — and every parent above then adds up the flipped values automatically. That automatic addition is what cascades the correction all the way to the top.
Step 3 — Build the display line item (this is what you show)
Add one more line item:
- Name:
Amount (Reporting) - Summary:
Formula - Formula:
IF 'SYS_AccountAttributes'.'Sign Flip for Net?' THEN Amount ELSE 'Amount (Calc)'
For the flagged subtree it shows the original positive Amount; everywhere else it shows the corrected engine value. Put this line item on your view, and hide Amount and Amount (Calc).
Step 4 — Why Summary = All is the piece that makes it work
I need the flag to be TRUE for the flagged leaves and their subtotal (Other Income), but FALSE at the grandparent — otherwise the grandparent would show the wrong number. Since I can't type into a parent cell, I let Summary = All derive it.
For a boolean, All makes a parent TRUE only if every one of its children is TRUE. Watch how it contains itself to exactly the right subtree:
Member | Its children | All result
|
|---|
Dividend Income (leaf) | — | TRUE (I input this) |
Misc Income (leaf) | — | TRUE (I input this) |
Other Income (subtotal) | both leaves TRUE | TRUE ✅ (auto-derived — no input!) |
Interest Expense | children all FALSE | FALSE |
Net Other Income & Expense (grandparent) | Other Income ✅, Interest Expense ☐ | FALSE ✅ (a FALSE child stops it) |
The TRUE flag climbs upward only as far as every sibling stays TRUE — exactly the Other Income subtree. The moment it hits the grandparent (which has non-flagged siblings), All returns FALSE and stops. So the flag lands on {the two leaves, Other Income} and is FALSE everywhere else, with zero parent input.
Step 5 — Verify
Member | Flag | Amount (original)
| Amount (Calc)
| Amount (Reporting)
|
|---|
Dividend Income | ✅ | 40 | −40 | 40 |
Other Income (subtotal) | ✅ | 40 | −40 | 40 (stays positive) |
Interest Expense | ☐ | 100 | 100 | 100 |
Gain on Extinguishment | ☐ | 0 | 0 | 0 |
Net Other Income & Expense | ☐ | 140 | 60 | 60 ✅ |
Operating Income (sibling) | ☐ | 1,000 | 1,000 | 1,000 |
Total | ☐ | 1,140 | 1,060 | 1,060 ✅ (cascaded!) |
Total moves 1,140 → 1,060 — a swing of 80, exactly 2 × 40, the sign flip flowing all the way up. Meanwhile Other Income still reads +40 on its own line. Both requirements met.
