BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Learn how to create, format and explore pivot tables within Excel based upon SSAS tabular models. This tutorial also shows how to create slicers, and combine them to create dashboards.
- Excel pivot tables in Analysis Services (Tabular Model)
- Setting up pivot tables in Excel (this blog)
- Refreshing a pivot table
- Formatting numbers in a pivot table
- Formatting a pivot table and choosing display options
- Drill-down and Quick Explore
- Slicers
- Things you can't do in tabular model pivot tables
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.
Posted by Andy Brown on 24 November 2015
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Setting up pivot tables in Excel
This blog explains how to get started with setting up a pivot table.
Beware: if you're used to creating pivot tables based on Excel data, you'll find it very strange that (when using tabular model data) you can't change the statistical function for measures or drag them into any part of a pivot table apart from the VALUES section.
Filters, columns, rows and values
When you create a pivot table, you can add one or more fields to each of the following sections:
You can tick the items you want to include in your pivot table field list, in which case they'll automatically go into the ROWS (for fields) or VALUES (for measures) sections, or you can drag them into the section of your choice.
The rules are shown below:
When you click on a measure (shown here in the red box), it can only appear in the VALUES section of the pivot table. When you click on an aggregation column/field, it will appear by default in the ROWS section but you can drag it from there to the COLUMNS or FILTERS section.
Here's an example of this:
If you click on Region, CentreType, Species and TotalQuantity, the first three will go in the ROWS section and the last one in the VALUES section.
You can drag the fields around to get this:
Here we're making the Region field a filter. It might have been easier to drag it into this section directly from the pivot table field list!
Here's the result of these choices:
This pivot table is summing the quantity of sales by species and centre type, for all regions.
To remove a field from a pivot table, just drag it off the appropriate section.
Filtering a pivot table
You can use the FILTERS field(s) to choose what your pivot table displays:
Click on the drop arrow to choose for which region(s) you are showing data.
it's all then pretty self-explanatory:
If you tick the Select Multiple Items box (as here), you can tick the boxes to display sales data for multiple regions. Here we're showing total sales in London and the North.
Your pivot table changes to reflect the choices you've made:
There were 19,106 total sales, but this has now gone down to 3,523, as we're only displaying data for two regions.
You can also use slicers to filter the results of a pivot table, as shown in a later part of blog. Many people find slicers easier to use than filters.
Choosing rows and columns to display
In addition to filtering a pivot table, you can exclude rows and columns that you don't like. Here's the option for rows:
Click on one of the two drop arrows ringed above, and choose what filter you want to apply.
In the following example, I'm showing all species apart from Amphibian, and all regions which contain the word North:
Excel changes the filter symbols to show that not all rows and columns are displayed.
You can clear a row or column filter like this:
Click on the filter arrow and choose this option to redisplay all of your data.
Sorting a pivot table
There are many ways to sort a pivot table - here's my favourite (it may not be yours!). First make sure you have the Data ribbon visible, then click on one of the sort tools:
Click to sort in ascending or descending order. What happens next depends on which cell you had selected (see below for examples).
Assuming that you started with this pivot table, here's what you might get:
Assume that you start with this pivot table.
If you have a row label selected, Excel will sort by rows:
|
|
| If you sort in descending order ... | ... you'll get this. |
If you have a column label selected, Excel will sort by columns:
|
|
| If you sort in descending order ... | ... you'll get this. |
Finally, if you have a single data cell selected, Excel will sort by that column:
|
|
| With this cell selected ... | ... sorting will give this. |
Moving rows and columns
Another way to change the order of rows and columns is to move them:
Think birds are more important than this? You can click on the column label, and click and drag on the border using the mouse pointer shape shown.
You can now drag the column to its new position (you could move a row up or down using the same procedure):
Drag the column to its new position, and release the mouse button.
Excel displays the revised pivot table:
Birds are now where they belong - on the left.
The next part of this blog looks at how to refresh the data or fields in a pivot table.
- Excel pivot tables in Analysis Services (Tabular Model)
- Setting up pivot tables in Excel (this blog)
- Refreshing a pivot table
- Formatting numbers in a pivot table
- Formatting a pivot table and choosing display options
- Drill-down and Quick Explore
- Slicers
- Things you can't do in tabular model pivot tables