Categories: Power BI
Tags:

✅ Power BI for Finance – Budget vs Actuals Analysis

Budget vs Actual compares what was planned to be spent or earned (Budget) with what was actually spent or earned (Actual).

It helps answer:

🔍 Did we stay on track financially or go over/under budget?

For example:

  • Budget for Marketing in July = $30,000
  • Actual spent = $35,000
  • → Variance = +$5,000 (overspent)

This analysis helps in cost control, forecasting accuracy, and financial decision-making.

📊 Dataset Overview

Here’s a sample of the dataset (200 rows total):

DepartmentCost CenterMonthBudgetActual
HRSouthJul-20244981135201
FinanceSouthFeb-20244120816456
ITSouthNov-20242515051212
FinanceEastOct-20241064441765
FinanceEastFeb-20244828637816

Categorical features:

  • Department
  • Cost Center
  • Month

📈 Power BI Dashboard – Budget vs Actual Analysis

🎯 Objective:

Track financial performance, highlight variances, and assist financial controllers in decision-making.

💡 Dashboard Visuals & DAX

1. Clustered Column Chart

Title: Budget vs Actuals by Department
Purpose: Compare budgeted vs actual spend by department.

Axis: Department
Values: Sum(Budget), Sum(Actual)

2. Line and Column Chart

Title: Monthly Trends – Budget vs Actual
Purpose: Track how actual and budgeted figures trend over time.

Axis: Month
Values: Sum(Budget), Sum(Actual)

3. Card Visuals

Title: Total Budget, Total Actual, Total Variance
Purpose: Show quick KPIs.

DAX Measures:

Total Budget = SUM(FinanceData[Budget])
Total Actual = SUM(FinanceData[Actual])
Total Variance = [Total Actual] - [Total Budget]

4. Matrix Table

Title: Department & Cost Center Variance
Purpose: Drill down variance by department and cost center.

Rows: Department, Cost Center
Values: Budget, Actual, Variance

DAX Measure:

Variance = SUM(FinanceData[Actual]) - SUM(FinanceData[Budget])

5. Bar Chart with Conditional Formatting

Title: Top 10 Variance Entries
Purpose: Identify departments or cost centers with highest overspend or savings.

Steps:

  • Create a calculated column to capture variance:
Variance Value = FinanceData[Actual] - FinanceData[Budget]
  • Use this column in the visual and sort descending.
  • Apply red-green formatting for positive/negative values.

6. Slicer Panel

Slicers:

  • Month
  • Department
  • Cost Center

Purpose: Enable filtering the dashboard for focused analysis.

🧮 Optional: Calculated Table

To show month-wise cumulative variance:

Cumulative Variance Table =
SUMMARIZE(
    FinanceData,
    FinanceData[Month],
    "Cumulative Budget", CALCULATE(SUM(FinanceData[Budget])),
    "Cumulative Actual", CALCULATE(SUM(FinanceData[Actual])),
    "Cumulative Variance", CALCULATE(SUM(FinanceData[Actual]) - SUM(FinanceData[Budget]))
)