Sorting and Filtering Pivot Report
Sort and filter button will automatically add into your Pivot Table once you start creating it. It is part of the feature in Pivot Table. The sorting feature confuses a lot of my student, many times. The first thing that confuses them what should they do first while sorting? I totally understand this. Simply because if you take a good look at the structure of the Pivot Table, as you add many more fields into the report, all you get it one Filter button for Row fields and another one for Column fields.
I will start real slow, so at the end of this section you have got to be confuse no more! I will be using the Pivot report as shown below as the example for this section.
a) If you’re going to sort the Category, you must have you’re your cursor being placed at least on one of the Category Item, then sort (A-Z) or (Z-A).
If you are going to sort by the Product Name, then you will have to place your cursor at least at one of the items, then start sorting.
If you’re going to sort the number, please understand that the sorting goes by either the “Category” or by the “Product Name”. If is is by the “Category”, which means the whole group of the Category will being sorted. This is how it looks like:
If you’re planning on sorting the values by the “Product Name”, this is only happening within the “Category”. Confuse? Check out the GIF below:
Now you understand what I’m trying to say?
b) To do the filter, this is a lil tricky. Especially while you’re having multiple field being stacked together, that tends to get people a little confuse too. Now, if you want to filter by the name or item, what I usually called the label, you need to make sure when you look for the filter setting, look into the label section.
To sort the by the “Category”, you must first make sure the filtering setting is being set to sort by the “Category”. Sames goes while you’re sorting by the “Product Name”, change the setting to “Product Name”. This is how you’re going to do it:
After you select from the dropdown, you can then proceed to filter the label.
If you’re planning to filter the Value, you will have to make sure you have selected the field from the dropdown before you start
Let’s talk about the TOP 10 filtering feature. I’m going to change the sample of data for this example. The report will be as shown as the below:
Let’s find the TOP 3 Product that generate the most sales.
With this setting :
This is what you’re going to get, the top 3 product that generate highest sales.
Now let’s try the total sales generate by the items that sums up close to the amount (It will try to get as close to the number specified by you) that you stated in the value placeholder, you can either try TOP or BOTTOM. Top means the result will return by summing all the top values whereas BOTTOM will return the result by summing all the bottom numbers. Let’s check out the GIF below:
One lat one, Sums by the Percent.
I’m making an example of Bottom | 20% | Percent| Sum of Sales
The sum of the sales of the bottom values that make up close to 20% of Grand Total.
The other example I’m making is TOP| 50%| Percent| Sum of Sales
The sum of the sales of the Top values that make up close to 50% of Grand Total