xmetricRecipes

Recipe Tableau and SQL

How do I compare budget to actual when the budget repeats on every row?

Take one budget value per department and month before you add anything up: SUM({ INCLUDE [department], DATETRUNC('month', [date]) : MIN([budget_amount]) }) in Tableau, or GROUP BY department and month in SQL. Summed raw, this theatre's budget looks like it was only 14% spent; counted once per month, it was 98% spent, and the department that looked thriftiest, Marketing, is the one over budget.

Updated

The data

Harbourline Community Theatre, invoices in 2025

Synthetic datatheatre-spend.csv

A community theatre with four departments. Each invoice row carries its department's monthly budget, the way a join from the budget sheet leaves it.

datedepartmentvendoractual_amountbudget_amount
2025-01-01MarketingRadio spot250.084200
2025-01-03MarketingFlyer drop326.624200
2025-01-03MarketingPhoto shoot187.054200
2025-01-04ProductionsKestrel Lumber2100.1112600

Step 1

Add up both columns, the obvious way

Every department looks wildly under budget, and Marketing, the department with the most invoices, looks the thriftiest. That ranking is the giveaway: the budget column is being counted once per invoice, so the more invoices, the bigger the fake budget.

Tableau

SUM([actual_amount]) / SUM([budget_amount])
// filter: MONTH([date]) <= {knob.through}

The xmetric engine ran this formula and got the SQL’s Budget amount column, row for row.

SQL

SELECT
  department,
  COUNT(*) AS invoices,
  SUM(actual_amount) AS actual,
  SUM(budget_amount) AS budget,
  SUM(actual_amount) / SUM(budget_amount) AS attainment
FROM input
WHERE month(date) <= 12
GROUP BY department
ORDER BY attainment

Rows

DepartmentInvoicesActual amountBudget amountBudget amount
Marketing212$80,981$1,246,8006.5%
Front of House90$57,481$437,00013.2%
Productions51$200,892$896,40022.4%
Education31$38,872$122,40031.8%

Why does the budget come out so big?

Because a join copies it. The budget sheet has one row per department and month; the invoice table has many. Join them and the month's budget lands on every invoice, so SUM adds it once per invoice. Summed raw, the theatre spent 14% of its budget. Counted once per month, it spent 98%.

Step 2

Count each month's budget once

At its own grain, department and month, the budget is a single number, so MIN, MAX or AVG all return it. The last column is what the plain SUM reported: the budget times the number of invoices. Productions had 5 invoices in January, so its 12,600 budget was counted 5 times.

Tableau

{ INCLUDE [department], DATETRUNC('month', [date]) : MIN([budget_amount]) }

SQL

SELECT
  department,
  date_trunc('month', date) AS month,
  COUNT(*) AS invoices,
  SUM(actual_amount) AS actual,
  MIN(budget_amount) AS budget,
  SUM(budget_amount) AS summed_budget_column
FROM input
WHERE month(date) <= 12
GROUP BY department, month
ORDER BY department, month

Rows

DepartmentMonthInvoicesActual amountBudget amountBudget amount
Education2025-01-01 00:00:002$1,650$2,800$5,600
Education2025-02-01 00:00:002$2,294$3,200$6,400
Education2025-03-01 00:00:003$3,229$4,000$12,000
Education2025-04-01 00:00:002$3,982$4,400$8,800
Education2025-05-01 00:00:003$4,059$4,800$14,400
Education2025-06-01 00:00:003$1,198$2,400$7,200
Education2025-07-01 00:00:003$3,147$2,000$6,000
Education2025-08-01 00:00:002$4,176$2,800$5,600
Education2025-09-01 00:00:003$4,262$4,800$14,400
Education2025-10-01 00:00:003$4,168$5,200$15,600
Education2025-11-01 00:00:002$3,304$4,800$9,600
Education2025-12-01 00:00:003$3,403$5,600$16,800
Front of House2025-01-01 00:00:008$3,086$3,500$28,000
Front of House2025-02-01 00:00:008$3,878$4,000$32,000
Front of House2025-03-01 00:00:008$5,030$5,000$40,000
Front of House2025-04-01 00:00:007$5,533$5,500$38,500
Front of House2025-05-01 00:00:008$6,035$6,000$48,000
Front of House2025-06-01 00:00:007$2,744$3,000$21,000
Front of House2025-07-01 00:00:009$2,207$2,500$22,500
Front of House2025-08-01 00:00:006$3,276$3,500$21,000
Front of House2025-09-01 00:00:007$5,821$6,000$42,000
Front of House2025-10-01 00:00:006$6,420$6,500$39,000
Front of House2025-11-01 00:00:007$6,198$6,000$42,000
Front of House2025-12-01 00:00:009$7,253$7,000$63,000
Marketing2025-01-01 00:00:0017$4,086$4,200$71,400
Marketing2025-02-01 00:00:0019$4,999$4,800$91,200
Marketing2025-03-01 00:00:0014$6,723$6,000$84,000
Marketing2025-04-01 00:00:0015$7,477$6,600$99,000
Marketing2025-05-01 00:00:0018$8,605$7,200$129,600
Marketing2025-06-01 00:00:0018$3,619$3,600$64,800
Marketing2025-07-01 00:00:0018$3,202$3,000$54,000
Marketing2025-08-01 00:00:0017$4,983$4,200$71,400
Marketing2025-09-01 00:00:0019$9,084$7,200$136,800
Marketing2025-10-01 00:00:0017$9,914$7,800$132,600
Marketing2025-11-01 00:00:0020$8,856$7,200$144,000
Marketing2025-12-01 00:00:0020$9,433$8,400$168,000
Productions2025-01-01 00:00:005$11,670$12,600$63,000
Productions2025-02-01 00:00:004$12,968$14,400$57,600
Productions2025-03-01 00:00:005$17,713$18,000$90,000
Productions2025-04-01 00:00:003$19,267$19,800$59,400
Productions2025-05-01 00:00:005$20,770$21,600$108,000
Productions2025-06-01 00:00:003$9,878$10,800$32,400
Productions2025-07-01 00:00:004$7,613$9,000$36,000
Productions2025-08-01 00:00:005$11,919$12,600$63,000
Productions2025-09-01 00:00:005$21,757$21,600$108,000
Productions2025-10-01 00:00:003$22,054$23,400$70,200
Productions2025-11-01 00:00:005$20,582$21,600$108,000
Productions2025-12-01 00:00:004$24,703$25,200$100,800

Common mistake

Summing a budget that repeats on every invoice

A join copies the coarser table onto every row of the finer one. Actuals are fine at invoice level; the budget is not, so it must be reduced to one value per department and month before it is added up.

WrongStarts with Education, Front of House, Marketing…
Point at a bar to read it.
0500K1000KEducationFront of HouseMarketingProductions

Tableau

SUM([budget_amount])

SQL

SELECT department, SUM(budget_amount) AS budget
FROM input
GROUP BY department
ORDER BY department
RightStarts with Education, Front of House, Marketing…
Point at a bar to read it.
0100K200KEducationFront of HouseMarketingProductions

Tableau

SUM({ INCLUDE [department], DATETRUNC('month', [date]) : MIN([budget_amount]) })

SQL

WITH m AS (
  SELECT
    department,
    date_trunc('month', date) AS month,
    MIN(budget_amount) AS budget
  FROM input
  GROUP BY department, month
)
SELECT department, SUM(budget) AS budget
FROM m
GROUP BY department
ORDER BY department

Who actually went over?

Step 3

Variance and attainment per department

The outer SUM adds one budget per month, so the department total is right at any level of the view. Variance is actual minus budget: positive means overspent. Slide the month back to June and watch Education: it looks comfortably under budget until the summer school bills land in July and August.

Tableau

SUM([actual_amount]) - SUM({ INCLUDE [department], DATETRUNC('month', [date]) : MIN([budget_amount]) })

SQL

WITH m AS (
  SELECT
    department,
    date_trunc('month', date) AS month,
    SUM(actual_amount) AS actual,
    MIN(budget_amount) AS budget
  FROM input
  WHERE month(date) <= 12
  GROUP BY department, month
)
SELECT
  department,
  SUM(actual) AS actual,
  SUM(budget) AS budget,
  SUM(actual) - SUM(budget) AS variance,
  SUM(actual) / SUM(budget) AS attainment
FROM m
GROUP BY department
ORDER BY attainment DESC

Rows

DepartmentActualBudgetBudgetBudget
Marketing80,98170,20010,781115.4%
Front of House57,48158,500−1,01998.3%
Productions200,892210,600−9,70895.4%
Education38,87246,800−7,92883.1%
112

The raw sum got the story backwards. Marketing pays many small bills (ads, flyers, print runs), so its budget was counted most often and it looked like it had spent 6.5%. In fact it spent 115.4% of its budget, the only department over for the year. The theatre as a whole came in under budget, which is exactly how one department's overrun hides inside a healthy total.

Why INCLUDE and not FIXED?

Common mistake

Using FIXED, then summing it anyway

FIXED returns the right budget on each row, but it still returns it on every row, so SUM adds it once per invoice again. INCLUDE works because the outer SUM adds one value per department and month, not one per row. Tableau relationships fix it at the source, by aggregating each table at its own level.

WrongStarts with Education, Front of House, Marketing…
Point at a bar to read it.
0500K1000KEducationFront of HouseMarketingProductions

Tableau

SUM({ FIXED [department], DATETRUNC('month', [date]) : MIN([budget_amount]) })

SQL

SELECT department, SUM(b) AS budget
FROM (
  SELECT
    department,
    MIN(budget_amount) OVER (PARTITION BY department, date_trunc('month', date)) AS b
  FROM input
)
GROUP BY department
ORDER BY department
RightStarts with Education, Front of House, Marketing…
Point at a bar to read it.
0100K200KEducationFront of HouseMarketingProductions

Tableau

SUM({ INCLUDE [department], DATETRUNC('month', [date]) : MIN([budget_amount]) })

SQL

WITH m AS (
  SELECT
    department,
    date_trunc('month', date) AS month,
    MIN(budget_amount) AS budget
  FROM input
  GROUP BY department, month
)
SELECT department, SUM(budget) AS budget
FROM m
GROUP BY department
ORDER BY department

Which months broke the budget?

Step 4

Find the months that broke the budget

A year total can hide a pattern. Marketing ran over in most months and drifted further over through the autumn season; Education was under budget all year except two summer months. Raise the tolerance and see which overruns are real habits and which are one-offs.

Tableau

IF SUM([actual_amount]) / SUM({ INCLUDE [department], DATETRUNC('month', [date]) : MIN([budget_amount]) }) - 1 > 0.05 THEN "Over"
ELSE "Within"
END

SQL

WITH m AS (
  SELECT
    department,
    date_trunc('month', date) AS month,
    SUM(actual_amount) AS actual,
    MIN(budget_amount) AS budget
  FROM input
  WHERE month(date) <= 12
  GROUP BY department, month
)
SELECT
  department,
  month,
  actual / budget - 1 AS variance_pct,
  CASE
    WHEN actual / budget - 1 > 0.05 THEN 'Over'
    ELSE 'Within'
    END AS flag
FROM m
ORDER BY department, month

Rows

DepartmentMonthVariance pctFlag
Education2025-01-01 00:00:00−41.1%Within
Education2025-02-01 00:00:00−28.3%Within
Education2025-03-01 00:00:00−19.3%Within
Education2025-04-01 00:00:00−9.5%Within
Education2025-05-01 00:00:00−15.4%Within
Education2025-06-01 00:00:00−50.1%Within
Education2025-07-01 00:00:0057.3%Over
Education2025-08-01 00:00:0049.2%Over
Education2025-09-01 00:00:00−11.2%Within
Education2025-10-01 00:00:00−19.9%Within
Education2025-11-01 00:00:00−31.2%Within
Education2025-12-01 00:00:00−39.2%Within
Front of House2025-01-01 00:00:00−11.8%Within
Front of House2025-02-01 00:00:00−3.1%Within
Front of House2025-03-01 00:00:000.6%Within
Front of House2025-04-01 00:00:000.6%Within
Front of House2025-05-01 00:00:000.6%Within
Front of House2025-06-01 00:00:00−8.5%Within
Front of House2025-07-01 00:00:00−11.7%Within
Front of House2025-08-01 00:00:00−6.4%Within
Front of House2025-09-01 00:00:00−3%Within
Front of House2025-10-01 00:00:00−1.2%Within
Front of House2025-11-01 00:00:003.3%Within
Front of House2025-12-01 00:00:003.6%Within
Marketing2025-01-01 00:00:00−2.7%Within
Marketing2025-02-01 00:00:004.1%Within
Marketing2025-03-01 00:00:0012.1%Over
Marketing2025-04-01 00:00:0013.3%Over
Marketing2025-05-01 00:00:0019.5%Over
Marketing2025-06-01 00:00:000.5%Within
Marketing2025-07-01 00:00:006.7%Over
Marketing2025-08-01 00:00:0018.6%Over
Marketing2025-09-01 00:00:0026.2%Over
Marketing2025-10-01 00:00:0027.1%Over
Marketing2025-11-01 00:00:0023%Over
Marketing2025-12-01 00:00:0012.3%Over
Productions2025-01-01 00:00:00−7.4%Within
Productions2025-02-01 00:00:00−9.9%Within
Productions2025-03-01 00:00:00−1.6%Within
Productions2025-04-01 00:00:00−2.7%Within
Productions2025-05-01 00:00:00−3.8%Within
Productions2025-06-01 00:00:00−8.5%Within
Productions2025-07-01 00:00:00−15.4%Within
Productions2025-08-01 00:00:00−5.4%Within
Productions2025-09-01 00:00:000.7%Within
Productions2025-10-01 00:00:00−5.8%Within
Productions2025-11-01 00:00:00−4.7%Within
Productions2025-12-01 00:00:00−2%Within
0%25%

Marketing went over by more than the tolerance in 9 months: a habit, not an accident.

Common mistake

Averaging the monthly percentages

An average of twelve monthly attainments gives a small month the same weight as a big one. Education's quiet months pull its average up to 86.8%, while it actually spent 83.1% of its budget. Divide the totals instead.

WrongStarts with Education, Front of House, Marketing…
Point at a bar to read it.
00.51EducationFront of HouseMarketingProductions

Tableau

WINDOW_AVG(SUM([actual_amount]) / SUM({ INCLUDE [department], DATETRUNC('month', [date]) : MIN([budget_amount]) }))

SQL

WITH m AS (
  SELECT
    department,
    date_trunc('month', date) AS month,
    SUM(actual_amount) AS actual,
    MIN(budget_amount) AS budget
  FROM input
  GROUP BY department, month
)
SELECT department, AVG(actual / budget) AS attainment
FROM m
GROUP BY department
ORDER BY department
RightStarts with Education, Front of House, Marketing…
Point at a bar to read it.
00.51EducationFront of HouseMarketingProductions

Tableau

SUM([actual_amount]) / SUM({ INCLUDE [department], DATETRUNC('month', [date]) : MIN([budget_amount]) })

SQL

WITH m AS (
  SELECT
    department,
    date_trunc('month', date) AS month,
    SUM(actual_amount) AS actual,
    MIN(budget_amount) AS budget
  FROM input
  GROUP BY department, month
)
SELECT department, SUM(actual) / SUM(budget) AS attainment
FROM m
GROUP BY department
ORDER BY department

Run it on your own data

Any table with a date, a group (department, cost centre, project), an actual amount and a repeated budget column works. Drop your CSV and pick the columns; the steps above recompute in your browser, and nothing is uploaded.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, theatre-spend.csv, shows the shape.

Questions

Why is my budget several times too big in Tableau?

The monthly budget was joined onto every invoice row, so SUM([budget_amount]) adds it once per invoice. A department with 18 invoices in a month gets its budget counted 18 times. Reduce the budget to one value per department and month first, with INCLUDE or a GROUP BY.

Which sign should variance have?

For costs, actual minus budget is positive when you overspend. For revenue, teams usually flip it so a positive number is good. Pick one, and say it in the column name.

Why is variance blank for a department with no spend?

Actual is NULL, and anything minus NULL is NULL. Use ZN(SUM([actual_amount])) in Tableau or COALESCE(actual, 0) in SQL. If budget and actuals live in separate files, join them with a FULL OUTER JOIN so unbudgeted spend and unspent budgets both survive.

How do I check pace in the middle of a month?

On the 10th, a month looks far under budget because two thirds of it is still ahead. Compare against a pro-rated budget for the current month: budget times days elapsed, divided by days in the month.

Sources