microsoft_excel

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.

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.

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.

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.

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 name and the column names
in the formula.

17. For the other 2 tables, replace the
Table name 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.

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.

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.

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])),

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])))

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.

facebook-comments

New! Comments

Have your say about what you just read! Leave me a comment in the box below.

Share this page:

What’s this?

Enjoy this page? Please pay it forward. Here's how...


Copyright © advanced-excel.com 2007 - 2019. All Rights Reserved. Privacy Policy

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.

advanced-excel.com is in no way associated with Microsoft

>