A pivot table is a tool that allows you to discover, summarize and explore your data interactively. Pivot tables can sort, count, unique count, average, or find the minimum or maximum of your data, and display the results in a new table showing the summarized data. This gives you a lot of flexibility and analytical power to extract the significance from detailed data sets.
Here is an example:
You have a list of closed deals and you want to see your total MRR by sales rep and by date. We can answer this question quickly using a pivot table.
Using the Transform
Click on the Add New button at the top right corner of your screen.
Select Metric from the Add New options.
- Choose the Dataset you need.
Ensure that the Data tab at the top of your screen is selected.
Click on Add Transform in the left panel.
Select Pivot Table from the Analyze list.
Fill in the details as needed.
The Row Source groups the data from the selected column into rows, which makes it easier to select as your chart's category labels. If you are using dates, most of the time you want to use that as the Row Source, grouped by day, week, month, quarter, or year.
The Column Source groups the data from that column into columns, which makes it easier to select as individual value series.
The Value Source is what populates the rest of the table with the numbers you want to aggregate by the row and column sources.
If you are using the Row or Column source with a date grouping, you have the option to "fill in missing dates," which inserts a row or column for the date group even if there is no data for that time period.
When you are grouping by week you also have the option to calculate the week starting on a Monday, instead of the default of starting on a Sunday.
There is also an option under the four fields to auto fill blanks with 0, which gives the value of zero for any cells with no data.
For instance, in our example if a sales rep did not make any sales on a specific day, the data would come in blank for that rep on that day, but we could display a zero to show that the revenue that day was zero.
In our example we want CloseDate as the Row Source, and Sales Rep in the Column Source. The Value Source is the MRR column, and we want to do a Sum as our Calculation Type. If you wanted to see the total number of sales by Sales Rep, rather than the dollar amount, you could change the Calculation Type to a Count, instead.
