This is the (sometimes annoying) feature that automatically creates a GetPivotData formula when trying to reference a cell inside a pivot table. In our case this will be the total orders for 2016 and the percent difference from 2016 to 2015.įirst, we need to turn on the GetPivotData feature. Now we can use the GetPivotData formula to extract the numbers from our pivot tables which will feature as the key performance indicator. We can add these by selecting the pivot table and going to the Analyze tab ➜ Subtotals ➜ Show all Subtotals at the Top of Group. We will also need subtotals in the first pivot table. This will calculate the percent difference from the current year to the previous year for each month.įor our second pivot table, we will add the Order Date Month grouping to the Rows area, the Order Date Year grouping to the Columns area and the Order Total field to the Values area.īoth pivot tables can then be filtered on the years to show only the two years of data which we want to compare. Then we can choose Years as our base field and Previous as our base item. We can right click on the numbers in our second Order Total field and then choose Show Values As ➜ % Difference From. For the second instance we will create a percent difference calculation.
The first instance of the Order Total in the Values area of our pivot table will be a regular SUM. If Excel does not group our order dates automatically, the dates will be listed down the rows of the pivot table and we can right click on them and choose the Group command from the menu. We will only need the year and month grouping, so we can remove the quarter grouping from the pivot table. This should group the order date into years, quarters and months automatically. This can be done by selecting a cell inside the order data and going to the Insert tab of the ribbon and selecting the PivotTable command.įor the first pivot table, we need to add the Order Date field into the Rows area and two instances of the Order Total field into the Values area. We will then need to create two pivot tables with this order data. This data contains the Order Date and Order Total which we will be using for the data cards. In this example I’m going to be using the AdventureWorks data set which has order data for a fictitious bike shop.