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.
- Download the working files
here.
- Go to the Actual worksheet.
- Convert the data into an Excel Table.
- Rename the Excel Table as Act.
- 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.
- Go to the Target worksheet.
- Convert the data into another Excel Table.
- Rename the Excel Table as Tgt.
- 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.
- Go to the Calendar worksheet.
- Convert the data into another Excel Table
- Name this table as Cal.
- 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.
- In the PowerPivot tab, click on the first icon
"PowerPivot window". You will be brought to the
PowerPivot Window.
- In the PowerPivot window, click on the icon "Diagram
View" located near the end of the Ribbon.
- 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.
- For the
other 2 tables, replace the Table namew and the column
names in the formula.
- 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.
- 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.
- In the PowerPivot tab, click on the icon "New
Measure" and Sum up the Amount in the Act Table.
- 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])))
- 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.
•
New! Comments
Have your say about what you just read! Leave me a comment in the box below.