---
title: "Compare a wide budget sheet to actuals | Oplema"
description: "Learn to unpivot a wide budget sheet with Transpose in Strata, leave out its Total column and pivot it back with Cross Tab to compare with actuals."
url: "https://oplema.com/learn/many-sources/compare-budget-to-actuals"
updated: "2026-11-04"
---

Lesson 8 of 9 · Lab

# Compare a wide budget sheet to actuals

Transpose melts a wide sheet into long rows of name and value, which you can filter and total. Cross Tab turns long rows back into columns. A wide sheet often has a column that isn't data, and melting it counts it like one.

In Transpose, **Key** columns repeat on every row. **Data** columns are melted into `Name` and `Value`. **Skip** columns are left out.

Marit's budget at [fictional Kramerica Bikes](https://oplema.com/learn/export-to-report/first-data-flow) has a row per shop and line, Hires or Repairs, a column per month and a `Total`, all net of VAT. She wants September's hires against what each shop took, with repairs beside it. The lower **Manual Input** holds September's net and repairs per shop, worked out from the till files and the workshop sheet. You build the net yourself in the next lesson.

6 rows with 12 months each should melt into 72 rows. Will they?

No, 78. `Total` is melted like a thirteenth month, so any sum over the long table counts the year twice.

## Melt, pick a month, pivot back

1. The lab is a live [Strata](https://oplema.com/strata) flow (Oplema's browser app for data prep) with **Transpose** selected: `shop` and `line` are **Key** and every month and `Total` are **Data**. The preview has 78 rows. **Cross Tab**, **Join** and **Formula** stay red until you set them up.
2. Select **Summarize**: a `budget` of 497800 for Hires, over 39 rows.
3. Find the column that isn't a month and keep it out of the melt. Check: **Summarize** reads 248900 over 36 rows. If you're stuck
   In **Transpose**, set `Total`'s **Role** to **Skip**.
4. **Select** casts Transpose's text `Value` to BIGINT, and **Filter** keeps `Name = 'Sep'`: 6 rows.
5. Select **Cross Tab** and set **Name Column** to `line` and **Value Column** to `Value`. **Group By** is `shop`, so you get a row per shop with `Hires` and `Repairs`.
6. Drag from **Cross Tab** to **Join**'s upper left dot. Its key is `shop` on both sides.
7. Select **Formula**, the last block: the variance, in cents, as its **Output type** is DECIMAL(18,2).

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What Formula should show

September against budget
| Shop | Hires budget | Net | Variance | Repairs budget | Repairs actual |
| --- | --- | --- | --- | --- | --- |
| Harbour | €14,000.00 | €12,938.00 | −€1,062.00 | €600.00 | €1,120.00 |
| Station Road | €21,500.00 | €21,168.80 | −€331.20 | €800.00 | €884.00 |
| Old Town | €6,000.00 | €6,163.20 | +€163.20 | €300.00 | €969.00 |

## Long to calculate, wide to read

Only Old Town beat its share of September's €41,500.00 hires budget, by €163.20. Harbour was €1,062.00 under and Station Road €331.20, net against a net budget.

Repairs sit beside the budget for reading, not as a variance: every shop spent more than planned. The long table was for filtering and summing; the wide one is for Marit, and a Total column belongs in what you show her, never in what you melt.

Your line for Marit: "Only Old Town beat its hires budget, by €163.20. All three spent more on repairs than planned."

See the [Transpose](https://oplema.com/docs/strata/transform/transpose) and [Cross Tab references](https://oplema.com/docs/strata/transform/cross-tab). For later: [Wide vs long data](https://oplema.com/learn/data-preparation/transpose-and-crosstab) compares the two shapes.

## Common reshaping mistakes

- **Melting a total.** A Total, Average or Notes column isn't another month. Skip it before melting.
- **Summing text.** If Summarize can't sum `Value`, cast it first.
- **Joining on a column the reshape removed.** After Cross Tab, the months are gone and `shop` is the only key left. Check the key exists on both sides.

## Unpivot and pivot in SQL and Excel

```
SELECT  shop,  line ,  value
FROM  (UNPIVOT budget  ON  COLUMNS( *  EXCLUDE (shop,  line , Total))  INTO  NAME  month  VALUE  value )
WHERE  month  =  'Sep' ;

PIVOT september  ON  line  USING  sum ( value )  GROUP BY  shop;
```

`EXCLUDE (Total)` is the Skip role. In Excel, Power Query's **Unpivot Other Columns** melts the sheet, and a PivotTable with `line` in Columns turns it back.
