Compare Actual with Target in Pivot Table

Comparing Actual with Target in Pivot Table always shows up the flaw of Pivot Table. Pivot Table can never do a good job because of the different sizes of the data set between Actual and Target numbers. However, with the launch of Power Pivot, this flaw no longer exist. This is because Power Pivot provided 2 useful features that Pivot Table does not have, combining 2 sets of data together without VLOOKUP and creating formulas within Pivot Table. If you have never heard of Power Pivot before, try googling to find out what it is. We are not going to cover it here. Instead, I am going to jump right in and show you how to prepare the actual vs target in pivot table.

1. Download the working files here.
2. Go to the Actual worksheet.
3. Convert the data into an Excel Table.
4. Rename the Excel Table as Act.
5. Go to the PowerPivot tab and click on the "Create Link Table" icon. The data will be upload into the PowerPivot window as Act Table.
6. Go to the Target worksheet.
7. Convert the data into another Excel Table.
8. Rename the Excel Table as Tgt.
9. Go to the PowerPivot tab and click on the "Create Link Table" icon. The data will be upload into the PowerPivot window as Tgt Table.
10. Go to the Calendar worksheet.
11. Convert the data into another Excel Table
12. Name this table as Cal.
13. Go to the PowerPivot tab and click on the "Create Link Table" icon. The data will be upload into the PowerPivot window as Cal Table.
14. In the PowerPivot tab, click on the first icon "PowerPivot window". You will be brought to the PowerPivot Window.
15. In the PowerPivot window, click on the icon "Diagram View" located near the end of the Ribbon.
16. Create 3 columns, one in each table by combining the Year and the Month column together. The formula for the Act Table should be =year(Act[Invoice Date])*100+MONTH(Act[Invoice Date]). The column name is PeriodID.
17. For the other 2 tables, replace the Table namew and the column names in the formula.
18. Link the 3 tables together by dragging the "Invoice Date" column in the Act Table to the "Date" column in Cal Table. Create a date column called TDate for Tgt Table by using the Date formula  =DATE(Tgt[Year],Tgt[MonthNo],1) and drag the "TDate" column in Tgt Table to the "Date" in the Cal Table.
19. Go back to the Excel window. In the PowerPivot tab, click on the Pivot Table. The Pivot Table created is different from the Pivot Table from the Insert tab.
20. In the PowerPivot tab, click on the icon "New Measure" and Sum up the Amount in the Act Table.
21. Create another measure called Target Sales and enter the following DAX formula into the measure =CALCULATE(SUM(Tgt[Target]),
    FILTER(ALL(Tgt[PeriodID]),CONTAINS(VALUES(Cals[PeriodID]),Cals[PeriodID],Tgt[PeriodID])),
    FILTER(ALL(Tgt[Name]),CONTAINS(VALUES(Act[Slsman]),Act[Slsman],Tgt[Name])))
22. Drag the Name into the Row Label box and the Actual Month into the Column Label box and your report will be able to align the Actual Sales with the Target Sales.