- Data Modeling with DAX Tutorial
- Home
- Data Modeling with DAX - Overview
- Data Modeling with DAX - Concepts
- Data Modeling Using Excel Power Pivot
- Loading Data into the Data Model
- Defining Data Types in the Data Model
- Understanding Data Tables
- Extending the Data Model
- Base Finance Measures & Analysis
- YoY Finance Measures & Analysis
- Variance Measures & Analysis
- Year-to-Date Measures & Analysis
- Quarter-to-Date Measures & Analysis
- Budget Measures & Analysis
- Forecast Measures & Analysis
- Count of Months Measures
- Ending Headcount Measures
- Average Headcount Measures
- Total Headcount Measures
- YoY Headcount Measures & Analysis
- Variance Headcount Measures
- Cost Per Headcount Measures & Analysis
- Rate Variance & Volume Variance

- Data Modeling with DAX Resources
- Quick Guide
- Useful Resources
- Discussion

- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who

# Variance Measures and Analysis

You can create variance measures such as variance to budget, variance to forecast, and forecast variance to budget. You can also analyze the finance data based on these measures.

### Creating Variance to Budget Sum Measure

Create Variance to Budget Sum measure (VTB Sum) as follows −

*VTB Sum:=[Budget Sum]-[Actual Sum]*

### Creating Variance to Budget Percentage Measure

Create Variance to Budget Percentage measure (VTB %) as follows −

*VTB %:=IF([Budget Sum],[VTB Sum]/ABS([Budget Sum]),BLANK())*

## Analyzing Data with Variance to Budget Measures

Create a Power PivotTable as follows −

- Add Fiscal Year from the Date table to Rows.
- Add the measures Actual Sum, Budget Sum, VTB Sum, VTB % from the Finance Data table to Values.

### Creating Variance to Forecast Sum Measure

Create Variance to Forecast Sum (VTF Sum) measure as follows −

*VTF Sum:=[Forecast Sum]-[Actual Sum]*

### Creating Variance to Forecast Percentage Measure

Create Variance to Forecast Percentage measure (VTF %) as follows −

*VTF %:=IF([Forecast Sum],[VTF Sum]/ABS([Forecast Sum]),BLANK())*

## Analyzing Data with Variance to Forecast Measures

Create a Power PivotTable as follows −

- Add Fiscal Year from the Date table to Rows.
- Add the measures Actual Sum, Forecast Sum, VTF Sum, VTF % from the Finance Data table to Values.

### Creating Forecast Variance to Budget Sum Measure

Create Forecast Variance to Budget Sum (Forecast VTB Sum) measure as follows −

*Forecast VTB Sum:=[Budget Sum]-[Forecast Sum]*

### Creating Forecast Variance to Budget Percentage Measure

Create Forecast Variance to Budget Percentage (Forecast VTB Percentage) measure as follows −

*Forecast VTB %:=IF([Budget Sum],[Forecast VTB Sum]/ABS([Budget Sum]),BLANK())*

## Analyzing Data with Forecast Variance to Budget Measures

Create a Power PivotTable as follows −

- Add Fiscal Year from the Date table to Rows.
- Add the measures Budget Sum, Forecast Sum, Forecast VTB Sum, Forecast VTB % from the Finance Data table to Values.