Pivot Table – Advanced Part 02



Hello all. This is the 2nd part of advanced pivot table section. If you missed reading the introduction and 1st part of advanced, check here and here.

Let’s use the same data set, as in the previous sections.

Let’s look at how sales has happened for the company month on month in terms of % (example: what % the sale has deviated when compared to the previous month). Let’s create the pivot with Date field in Rows and Revenue in Values. To have a better view of sale comparison, let’s have revenue column twice. So please add Revenue into Values section again.

Please note when you move the Date, pivot automatically cuts the ROWS section into months and Date. For our example, let’s keep the first revenue column (column# B) as the actual sales number and the 2nd revenue column (column# C) as the comparison column.

Right click anywhere on the 2nd revenue column -> Show Values as -> Difference From.. to bring the show values as dialog box. Keep the base field as “months” or “date” (field what we have in the rows section – in our data set we don’t two different years hence we don’t have years as an attribute). Base item should be previous and hit ok. All shown below

Now that we have the revenue column twice, if one needs to see the revenue as both numbers and %, you can right click on one of the column ->show values as -> % of Grand Total.

Next, let’s look at how to get the distinct value. For example let’s find out which product sold in how many regions. In our case we had 4 regions and let’s see if there is any product which is not sold in any region.

Note: For this, we need to convert our pivot as Data model:

  1. Inset Pivot for the data set. Ensure you check “Add this data to Data model” while creating the pivot in this step
  2. Add Product in the Rows and region in the Values
  3. Click on the Values and select Value Field Settings and select Distinct count
  4. Now, you can see the products and their presence in number of regions

Next, let’s look at another interesting feature “Calculated Field”. For instance let’s calculate the profit out the revenue generated. In our data set, we have the Production cost and number of units sold (as Sale unit) – I have added another column as Manu. Cost which is sale unit*prod.cost. To calculate profit, let’s not add any formula – Pivot will give it easily for us.

  1. In our pivot table, let’s add Product and variant under Rows and revenue under values
  2. Right click anywhere on the pivot table -> under PivotTable Analyze tab -> Fields, Items & Sets -> Calculated Field
  3. Give a name, let’s say Profit. Under the formula, we have the list of fields. Let’s choose revenue, hit “Insert Field”. Enter “-” and then click on Manu. Cost and hit “Insert Field” again.
  4. Click “Add” to insert a new column that would calculate the profit for each variant.

You can use the usual conditional formatting on all the pivot tables

Video Tutorial

I hope you liked this tutorial. Please let me know your feedback to improve the content. Thanks for reading.



Leave a Reply

%d bloggers like this: