A tutorial on using pivot tables in Excel based on SSAS tabular models
Part two of an eight-part series of blogs

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.

  1. Excel pivot tables in Analysis Services (Tabular Model)
  2. Setting up pivot tables in Excel (this blog)
  3. Refreshing a pivot table
  4. Formatting numbers in a pivot table
  5. Formatting a pivot table and choosing display options
  6. Drill-down and Quick Explore
  7. Slicers
  8. 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: 

Parts of pivot table

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:

Measures and aggregated fields

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:

Four example columns

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:

Dragging fields

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:

Pivot table result

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:

Filtering by region

Click on the drop arrow to choose for which region(s) you are showing data.

 

it's all then pretty self-explanatory:

Select multiple items

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:

Selected data

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:

Row/column filters

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:

North, but not amphibians

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:

Clearing filters

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:

Sorting

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:

Initial pivot table

Assume that you start with this pivot table.

 

If you have a row label selected, Excel will sort by rows:

Sort rows before Sort rows after
If you sort in descending order ... ... you'll get this.

If you have a column label selected, Excel will sort by columns:

Sort columns before Sort columns after
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:

Sort cells before Sort cells after
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:

Moving a column

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):

Moving a column part 2

Drag the column to its new position, and release the mouse button.

 

Excel displays the revised pivot table:

Birds on the left

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. 

  1. Excel pivot tables in Analysis Services (Tabular Model)
  2. Setting up pivot tables in Excel (this blog)
  3. Refreshing a pivot table
  4. Formatting numbers in a pivot table
  5. Formatting a pivot table and choosing display options
  6. Drill-down and Quick Explore
  7. Slicers
  8. Things you can't do in tabular model pivot tables
This blog has 0 threads Add post