A plan in any cut, without rebuilding the report
Plan allocation — three layers
A plan is set by month, by brand, by rep — but the questions come about the day, the SKU and the client. Here's the method that spreads a single plan so it stays comparable to actuals in any cut — without a manual rollup every week.
A "plan per client" that isn't in the data is a measure. Month→day and brand→SKU are materialized in Power Query; rep→client is computed in DAX for the current filter. Below — the M code, the measures and a live plan/actual.
01 The problem: plan and actual speak different languages
It's convenient to set a plan coarsely: by month, by brand, by rep. But the questions come fine-grained: "are we on plan this week", "how much of the plan is hanging on this client", "how's the team lead doing this quarter". Between the plan and those questions sits a manual rollup that someone assembles from scratch every time.
Worse, the plan is alive: targets get moved between reps and brands through the year. Every revision means rewriting history across several spreadsheets without breaking anything. That's exactly where it usually breaks.
02 The principle: one plan, any cut
The plan is entered once — in Google Sheets, in a convenient "wide" form. Then the model brings it to a common shape, spreads it across days and links it to the actuals through references. After that plan and actual filter by the same relationships — and "% attainment" computes correctly at any level. Turn the filters:
| Rep | Team lead | Plan, $ | Actual, $ | % attainment | Plan, units | Actual, units |
|---|
* "% attainment" = actual ÷ plan. Scale: ≥100% green · 85–99% amber · <85% red. Laggards floated to the top.
03 The plan shows up where it was never set
The most useful part starts when you need a cut the plan was never set on. The classic one is the client: the plan is on a rep, but the manager needs to know which clients that plan "hangs" on. The model spreads the rep's plan across their clients in proportion to their share of actual sales — the client totals stay equal to the plan, nothing is lost or double-counted.
04 What it changes
Plan revision becomes an edit in Google Sheets: change a number or rewrite a rep in a column, hit "Refresh all" — and plan, actual and percentages re-assemble themselves. One screen closes dozens of questions — by team lead, rep, brand, channel, client and period — without rebuilding the report.
01 Three allocations
"Allocation" here means spreading a coarse plan onto a finer cut. There are three, and they differ fundamentally in where they're computed:
- Month → day. Materialized in Power Query: the monthly plan is divided by the number of days in the month.
- Brand → SKU. Also in Power Query: the summary plan is spread by each SKU's share of recent sales.
- Rep → client. In DAX, at query time: the plan is spread under the current filter by share of actual.
The dividing rule is simple: what doesn't depend on the cut — precompute at refresh; what depends on the user's choice — leave to a measure.
02 Month → day
Actuals arrive by day, the plan by month. To make them comparable on any calendar cut, the monthly plan is "spread" across days: a join with the Calendar on the first day of the month and a divide by the number of days.
-- Daily plan = monthly ÷ days in month WithCalendar = Table.NestedJoin(Parsed, {"Plan month"}, Calendar, {"FirstDayOfMonth"}, "Cal", JoinKind.LeftOuter), Expanded = Table.ExpandTableColumn(WithCalendar, "Cal", {"Date", "Quarter", "DaysInMonth"}), Daily = Table.AddColumn(Expanded, "Daily Plan", each [Value] / [DaysInMonth])
From then on any period is a plain sum, with no "but February has 28 days" caveats.
03 Brand → SKU by share of sales
The summary plan is set on a brand — to compare it to actuals by item, it's spread in proportion to each SKU's share of the brand's sales over recent months. The plan "self-tunes" to the current product mix, not a stale reference.
-- SKU share within a brand → brand plan split onto SKUs WithShare = Table.AddColumn(Expanded2, "$ share", each [#"$"] / [#"Brand $"], type number), PlanUSD = Table.AddColumn(Expanded3, "Sales plan ($)", each [Daily Plan] * [#"$ share"], type number)
04 Rep → client in DAX
There's no client in the plan — so it can't be allocated in advance (too many cuts). A measure does it: it takes the plan in the current context and multiplies by the client's share of actual.
-- [Plan $] — spread the plan onto the current cut by share of actual IF( [Sold $], SUM('Plans'[Sales plan ($)]) * DIVIDE( [Sold $], CALCULATE([Sold $], ALL('Dim_Clients'[Client])), 0 ) )
For a single client DIVIDE returns its share; with no client filter the share equals one — and the measure returns the full plan. The client totals always equal the rep's plan.
05 Plan/actual and % attainment
Because the plan is brought to daily granularity and linked to the Calendar, any period is just a filter. And plan attainment is the ratio of actual to plan, correct at any level, because both quantities filter by the same relationships.
-- Quarterly cut and % attainment Plan $ 2026 Q2 = CALCULATE( SUM('Plans'[Sales plan ($)]), FILTER( ALL('Calendar'), YEAR('Calendar'[Date]) = 2026 && MONTH('Calendar'[Date]) >= 4 && MONTH('Calendar'[Date]) <= 6 )) % of plan $ = DIVIDE( [Sold $], [Plan $] )
Let's build plan/actual on your data
In a free review we look at how your plan lives today and show how to match it against actuals in any cut — from team lead down to client and day. No obligation.