Budget vs Actual analysis in Power BI allows organizations to compare planned financial targets (budget) against realized performance (actuals). This helps track performance, identify variances, and make informed decisions to stay on track financially.
It is widely used in finance, operations, and project management dashboards.
Why Budget vs Actual Analysis is Important
- Highlights areas where performance exceeds or falls short of expectations
- Provides insights for cost control and resource allocation
- Helps in forecasting and planning future budgets
- Supports data-driven decision-making
Key Metrics
- Budget Amount: Planned revenue, cost, or expense
- Actual Amount: Real revenue, cost, or expense
- Variance: Difference between actual and budget
- Achievement %: Actual as a percentage of the budget
Steps to Create Budget vs Actual Analysis
Step 1: Prepare the Data
- Dataset should include budget and actual figures with dimensions like department, project, region, or month
- Include a Date table for time-based comparison
Step 2: Create Measures Using DAX
Actual Amount
Actual Amount = SUM(Finance[Actual])
Budget Amount
Budget Amount = SUM(Finance[Budget])
Variance
Variance = [Actual Amount] - [Budget Amount]
Achievement %
Achievement % = DIVIDE([Actual Amount], [Budget Amount], 0)
Step 3: Visualize the Metrics
- Column or Bar Chart: Compare budget vs actual by category, department, or month
- Waterfall Chart: Show how budgeted amounts translate into actuals and variances
- KPI Cards: Display total budget, actual, variance, and achievement %
- Conditional Formatting: Highlight over- or under-performance
Step 4: Add Interactivity
- Use slicers for filtering by period, department, region, or project
- Enable drill-through to see transaction-level details
- Dynamic titles using DAX to reflect selected filters
Example Scenario
- Dashboard: Monthly Budget vs Actual Performance
- KPIs: Total Budget, Total Actual, Variance, Achievement %
- Visuals:
- Column chart comparing budget vs actual by department
- Waterfall chart showing variance composition
- KPI cards highlighting over- or under-achieving areas
Best Practices
- Keep budget and actual data aligned by period and category
- Use dynamic measures for flexible analysis across different dimensions
- Apply conditional formatting to make variance and performance visually clear
- Regularly refresh datasets to ensure data accuracy
- Combine summary KPIs with detailed charts for actionable insights
Conclusion
Budget vs Actual analysis in Power BI helps organizations monitor performance against plans, detect deviations early, and make informed financial decisions. With DAX measures, interactive visuals, and conditional formatting, users can easily track progress and manage resources effectively.