Derived Metrics: letting VCs build their own metrics on top of existing data

Analysts and VCs don't just consume the metrics a company reports. They build their own on top of them: Rule of 40, Revenue per Employee, sector-specific ratios like RASM for airlines. PortfolioIQ had no way to let someone define one of these and have it behave like a native metric: chart it, roll it up, trust it. So that math happened in spreadsheets outside the tool, at the exact moment we should have been most useful.

Why it mattered: two signals made this a priority, not a nice-to-have. Greenoaks asked for it directly. Their team was already computing metrics like this outside PortfolioIQ. And internal usage research traced almost every analyst question back to the same root: how is this company actually performing? That question nearly always meant combining two or more raw metrics.

A customer ask and internal usage research both pointed the same way. The third signal was a competitive teardown, to check whether the market had already converged on a shared mental model, and how much interaction-design risk we were taking on.

Concrete use cases we validated against, spanning sector-specific and cross-sector needs, so the design targeted a general arithmetic engine rather than a handful of hardcoded formulas.

One story, building analyses on custom metrics, was explicitly descoped for v1. I designed the data model so that door stays open, without paying its cost now.

The riskiest part of this feature wasn't the math. It was making formula entry feel safe for a non-technical, deadline-driven user. Free-text input invites typos and silent errors, so the design leans on constrained, block-based input instead.

Once users chain formulae on top of formulae, the real design work stops being about the happy path. I owned the states below, and closing them was a gating item before engineering could build.

Each state gets its own compact cell plus an explanatory tooltip, so a user scanning a table full of derived metrics can tell "genuinely zero" apart from "broken" apart from "too large to be meaningful," at a glance.

Derived metrics can reference other derived metrics, so the system needs a strict evaluation order and must actively reject formulas that would create a cycle. I treated this as a validation-time problem, not a runtime one: saving a formula runs a dependency check and blocks with a clear explanation, rather than saving it and failing silently at computation time.

If B in A = B + C gets remapped to D during tagging, the formula must update to A = D + C automatically. Otherwise a derived metric silently points at the wrong data. This is the practical payoff of storing formula dependencies as structured references rather than strings: a remap becomes a reference update, not a find-and-replace.

Derived metrics surface everywhere a native metric does: the all-metrics list, the metric detail page (sibling-metric context plus a live-substitution tooltip), and an equivalent Admin UI. Deliberately excluded from v1: the tagging tool and custom analyses, scoped out to ship faster with a data model built so neither is a rewrite later.

60% adoption in one release with no marketing push means the feature reached past its original requester to become a default workflow. And 12–13 metrics per org means firms aren't creating one-off formulas and stopping, they're standardizing on a set, exactly the behavior the usage research predicted.

The hardest part wasn't the formula UX. It was resisting scope creep toward "let users write any function," and constraining hard to arithmetic-only, block-based input instead. That constraint is what made the error states, cycle detection, and mapping-safety guarantees tractable in one release rather than an open-ended research project.

V1 answered a narrow question well: can one investor define one arithmetic formula on one company? Usage exposed a harder problem underneath. The teams who ingest and standardize data across 150+ portfolio companies were hitting the same ceiling: one static formula, no conditionals, no per-granularity logic, no way to define a metric once and roll it out everywhere.

V2 is a three-pillar upgrade to the same system: a richer formula language (conditionals, granularity scoping, derived-to-derived chaining to depth 5), a more capable computation engine (roll-up participation, dependency-graph execution, granular error reporting), and a bulk creation system sequenced deliberately as a later phase, once the first two hardened. All three have since shipped.

Five internal personas, one recurring pattern: doing it in Excel because the tool can't. Grouped by who was affected rather than by feature area, since the same missing capability shows up as a different problem depending on who hits it.

With five personas and a wide spread of problems, the risk was solving each complaint in isolation. Instead, every user story went through the same discipline: propose 2–3 candidate solutions, score each on feasibility, desirability, and viability, then map survivors back to a small set of outcomes the whole tree needed to serve: eliminate spreadsheet workarounds, make derived metrics trustworthy and debuggable, and scale standardization without per-company toil.

Run across all 23 stories, this consistently favored text-based editing over visual canvases, and pushed bulk creation out into its own follow-on phase rather than folding it into the core editor.

Conditional logic is the headline addition: IF...THEN...ELSE with an EXISTS check for gap-filling.

Granularity-scoped fallbacks solve the annualization problem directly. The same fallback chain now carries a different formula per granularity instead of one formula misapplied to all of them. And derived metrics can reference other derived metrics up to a dependency depth of 5, resolved via topological sort, for example Rule of 40 = RevenueGrowth[t] + EBITDAMargin[t], where both inputs are themselves derived.

Derived metrics now participate in roll-ups, extending population priority from V1's two steps to three: reported value, rolled-up reported metric, then rolled-up derived value itself. Execution runs as a full dependency-graph traversal, and the three V1 failure states split into distinct, logged reasons per period instead of one blank cell. The "-" state matures into an actual diagnostic.

An admin defines a formula once at the org level (a Global Derived Metric Template, or GDMT), which resolves to every company's local mappings automatically, with a downloadable resolution report, auto-apply to new companies, and a per-company "reset to GDMT" action. This is the piece that turns "define a metric 150 times" into "define a metric once."

The biggest shift from V1 to V2 wasn't the formula language. It was who the user became. V1 was designed for one investor building one metric; V2 had to hold up for an operations team standardizing hundreds of metrics across a portfolio, where a silently-broken formula doesn't just confuse one person, it corrupts a fund-level number. That's why the guardrails exist as hard limits rather than warnings, and why cycle detection errs conservative even at the cost of occasionally rejecting a formula that would've worked fine.