• Plan revision = rebuilding everything. Every change meant manually redistributing targets across departments and brands — a huge time sink.
• No relational links — no rollup. The plan and actual tables weren't connected. "Plan vs actual by brand in a channel" was assembled by hand weekly.
• The visuals were weak. Without a data model any summary is a static sheet for one question. A new cut meant digging through exports again.
• Reassigning an owner — a disaster. Rewriting history across several tables without breaking anything — a source of errors and lost time.
• Different planning depth. Some brands were planned in detail, by item; others in broad strokes by brand and channel. Reconciling the two approaches into one actual-comparable picture by hand is nearly impossible.

Architecture

The principle is the same as in the client's other modules: pull only raw data out of QuickBooks with a simple export, and keep all the logic — normalization, plan allocation, plan/actual computation — in the Excel model. The plan, meanwhile, is set not in QuickBooks and not in code, but in Google Sheets.

QuickBooks here is neither an integration nor a customization: the manager exports sales with the standard "to file" button. All the "dirt" in those exports (junk rows, casing, naming inconsistencies, total rows) is cleaned up inside the M code. The plan doesn't pass through QuickBooks at all — it lives in Google Sheets and is pulled straight from there.

Two plan-entry modes — one output table

Management plans differently depending on the brand, and breaking that habit would be a mistake. So Google Sheets has two sheets, and the model brings them to a common shape itself.

The final Plans query is a merge of two normalized branches into a table with identical columns: Brand, Channel, Rep, Date, Quarter, SKU, Plan ($), Plan (units).

-- "Plans" query (final): union of the two planning modes
let
    Source = Table.Combine({
        #"Plans without item detail",   -- summary: brand × channel × rep
        #"Plans with item detail"       -- detailed: by SKU
    }),
    Types = Table.TransformColumnTypes(Source, {
        {"Sales plan ($)",    type number},
        {"Sales plan (units)", type number}
    })
in
    Types

Parsing the multi-row Google Sheets header

In the plan table management keeps data in a "wide" form that's easy on the eye: brand/SKU on the left, and the header is several rows (month, channel, rep, plan type $/units). The model needs a normal (long) form. The trick: glue the header rows into one composite heading via an @ delimiter, unpivot the table, then split the heading back out.

-- Build a composite heading from several header rows
Headers = Table.SelectRows(
    Table.Transpose( Table.FirstN(Cleaned, 4) ),   -- 4 header rows
    each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
),
Combined = Table.CombineColumns(
    Table.FillDown(Headers, Table.ColumnNames(Headers)),
    Table.ColumnNames(Headers),
    each Text.Combine(_, "@"), "Combined"
)[Combined],

-- Unpivot "wide" to "long" and split the heading back out
Long   = Table.UnpivotOtherColumns(Tbl,
    {"Brand", "Item.SKU", "Item"}, "Attribute", "Value"),
Parsed = Table.SplitColumn(Long, "Attribute",
    Splitter.SplitTextByDelimiter("@", QuoteStyle.Csv),
    {"Plan month", "Sales channel", "Rep", "Plan type"})

Allocation #1: month → day

Management sets the plan by month, but actuals arrive by day. To make them comparable in any cut (day, week, month, quarter), the monthly plan is "spread" across days: the query joins the Calendar on the first day of the month and divides the value by the number of days.

-- Join Calendar; 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])

This step turns the compact GS plan into a daily table: with several brands, channels and reps, a year adds up to over a million rows. But from then on any period is a plain sum, with no "but February has 28 days" caveats.

Allocation #2: brand → SKU (by share of sales)

On the summary sheet the plan is set on a brand and channel, with no item split. To compare it to actuals by SKU, we spread the plan in proportion to each SKU's share of the brand's sales over the last 5 months — the plan "self-tunes" to the current product mix. First a separate query computes the weights.

-- SKU weight within a brand (sales over the last 5 months)
ByBrand = Table.Buffer( Table.Group(Recent, {"Brand"},
    {{"Brand $", each List.Sum([#"Sales ($)"]), type number}}) ),
BySKU   = Table.Group(Recent, {"Item.SKU", "Brand"},
    {{"$", each List.Sum([#"Sales ($)"]), type number}}),
WithShare = Table.AddColumn(Expanded2, "$ share", each [#"$"] / [#"Brand $"], type number)

Then the summary plan is multiplied by these weights — the brand target is broken out into specific SKUs, and via the average price also converted into units.

-- Brand plan → SKU plan in proportion to share of sales
PlanUSD = Table.AddColumn(Expanded3, "Sales plan ($)",
    each [Daily Plan] * [#"$ share"], type number),
PlanUnits = Table.AddColumn(PlanUSD, "Sales plan (units)",
    each [#"Sales plan ($)"] / [Unit price], type number)

Relational model: a star instead of manual rollups

The main thing missing before was relationships. Now the Plans table is the fact table at the center of a star, surrounded by lookups that filter it automatically.

Relationships give the whole point of the exercise: a filter on any lookup automatically reaches both the plan and the actual. Pick a team lead — plan and actual recompute across all their reps; pick a quarter — both tables filter. The Rep → Team lead relationship gives roll-up along the hierarchy with no manual summary.

Allocation #3: rep's plan → client (dynamically, in DAX)

The third allocation works not at refresh but at query time. The plan is set on a rep, brand and channel — there's no client in the table. But a manager needs a cut by client. The data has no such answer — a measure builds it, spreading the plan in proportion to each client's share of actual sales.

-- [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
    )
)

The measure takes the plan in the current context and multiplies by the client's share of sales. For a single client DIVIDE returns its share; with no client filter the share equals one — and the measure returns the full plan. The same trick spreads the plan onto any cut with no direct plan.

Plan for any period and % attainment

The plan is brought to daily granularity and linked to the Calendar, so any period is a matter of a filter. Quarterly measures are an ordinary CALCULATE with month bounds, and plan attainment is the ratio of actual to plan, correct at any level of detail.

-- [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 $] and [% of plan units]
DIVIDE( [Sold $],    [Plan $] )
DIVIDE( [Sold units], [Plan units] )