How to Set Up Target and Actual Sales in Excel Power Pivot
Question details
The user wants to structure and compare target and actual sales data by stream and month using Excel Power Query and Power Pivot.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing monthly sales performance targets against actual achievements using a unified Data Model.
- Observed behavior
- Setting up proper data tables, dimension relationships, and DAX calculations to visualize variance in a PivotTable or Power BI.
Ensure you have the Power Pivot add-in enabled in your Excel application and your raw sales data is organized in a clear, tabular format.
Build a Target and Actual Sales Data Model
This comprehensive method guides you through structuring your sales data, establishing relationships, and creating DAX measures for variance calculation.
To effectively analyze target versus actual sales, you must load separated fact tables into the Data Model and bridge them using shared dimension tables (like Date/Calendar and Stream).
Create two separate Excel tables named 'Target Sales' and 'Actual Sales'. Ensure both tables share a consistent column structure, such as 'Stream', 'Month' (formatted consistently like MMM YYYY), and 'Value'.
Select each table and navigate to Data > From Table/Range. Rename the queries to 'TargetSales' and 'ActualSales'. Verify your data types (e.g., Month as Date, Value as Decimal). Click Close & Load To > Only Create Connection, and check the box to 'Add this data to the Data Model'.
Create a 'Calendar' table containing all continuous months in your data, and a 'Stream' table containing unique sales streams. Load both into Power Query and add them to the Data Model. In Power Pivot, mark the Calendar table as a Date Table.
Open the Power Pivot window and go to Diagram View. Drag to create relationships between TargetSales[Month] and Calendar[Month], and ActualSales[Month] and Calendar[Month]. Repeat this linking process for the Stream columns to your unique Stream dimension table.
In the Power Pivot calculation area, create measures for your analysis: Total Target := SUM(TargetSales[Value]), Total Actual := SUM(ActualSales[Value]), Variance := [Total Actual] - [Total Target], and Variance % := DIVIDE([Variance], [Total Target]).
Go to Insert > PivotTable and select 'From Data Model'. Use Calendar[Month] and Stream[Stream] for your rows or columns, and add your newly created DAX measures into the Values area to analyze the performance variance.
Need a lightweight alternative for spreadsheet analysis?
If you are looking for an intuitive, fast, and feature-rich spreadsheet application, WPS Office is an excellent free alternative. It effortlessly handles Microsoft Excel files and standard PivotTables while offering a smooth user experience.
- 1. Download WPS Office: Visit the official WPS website and download the free WPS Office suite for your operating system.
- 2. Install and Launch: Run the installer and open the WPS Spreadsheet application.
- 3. Open your Excel Workbook: Drag and drop your existing .xlsx file into WPS Spreadsheet to continue analyzing your sales data seamlessly.
Frequently Asked Questions
Why do I need a separate Calendar table for Target and Actual sales?
A separate Calendar (or Date) dimension table acts as a bridge, allowing you to filter and compare data from both the Target and Actual sales fact tables simultaneously without missing dates or creating inaccurate totals.
Can I combine Target and Actual sales into a single table instead?
Yes, you can append the data into one table by adding a 'Scenario' column (e.g., labeling rows as Target or Actual). However, keeping them separate and linking them via dimension tables is often more flexible for complex Data Models.
Why are my DAX measures returning blank or incorrect values in the PivotTable?
This usually happens if the relationships between your sales tables and dimension tables are not properly defined in the Power Pivot diagram view, or if the data types for the linked columns do not match.
How do I format the Variance percentage in Power Pivot?
In the Power Pivot window, select your Variance % measure in the calculation area at the bottom of the data view, navigate to the Home tab, and click the Percentage icon to apply the correct formatting.
