Build a Power BI budget vs. actual report from Excel
Turn a monthly Excel budget and exported transactions into a Power BI report you can refresh and reconcile at month end.
Set up your budget and actuals files
Use one table for the approved budget and a separate table for actual transactions. Give the budget a month, department or cost center, account, and amount. Give the actuals a posting date, department, account, amount, and transaction-line ID. Keep source IDs as text so leading zeros survive import.
If your workbook has a heading for each month, move those headings into rows in Power Query. The department and account repeat on each monthly record. Start with a single budget version, one reporting currency, and a consistent sign convention across the two files.
- In Power BI Desktop, choose Get data, connect to the Excel workbook and the actuals export, and select Transform data.
- Remove subtotal and header rows. Select the budget’s month columns and choose Unpivot only selected columns. Rename the output columns Month and Amount, and include the year in every month value.
- Assign date and amount types explicitly. Trim stray spaces from department and account keys, and map them to the same identifiers in both files.
Put both sources on the same report
Keep one budget record per month, department and account after aggregating any planned splits. Preserve the separate actual transaction rows so you can trace the number on a report back to the source file.
Connect both tables to shared month, department and account lookups. Use lookup fields for the report slicers and matrix rows. Put a sum of each source’s amount in the matrix, then add an actual minus budget measure.
Check a known total before you refresh
Open the budget vs. actual sample and filter to April 2026, Marketing, and Campaigns. The budget is $10,000 and the actual is $12,000. The dollar variance is $2,000, and the expense is 20% over budget. The transaction detail for that selection should add up to $12,000.
For your own files, compare the month-end totals in the report with the source files, using the same month and department filters. Review unmatched account keys, nonnumeric amounts, and repeated line IDs before treating the report as final.
Try this with the sample report
Use the same budget and transaction files to check the April Marketing results.
