A tutorial on using pivot tables in Excel based on SSAS tabular models
Part six 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
  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 (this blog)
  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.

Drill-down and Quick Explore

This part of the tutorial shows two ways in which you can analyse a particular number further: by drilling-down to show the underlying numbers, or by using Quick Explore to break it down by the values in a particular dimension.

If you're wondering why you haven't seen Quick Explore before, it only shows up when you have a pivot table based on certain data sources (of which a tabular model is one).

 Double-clicking to drill down

The pivot table below shows that you've sold 364 amphibian products in retail parks, but you want to know more:

Double-click to drill down

We want to know the break-up of these 364 sales.

You can drill-down to show the items which constitute this figure by choosing the following menu option (or you can just double-click on the cell):

Showing details

You can right-click and choose this menu option, but it's much more fun just to double-click on the cell.

However, what you get isn't particularly helpful:

Underlying data

The First 1000 rows title is misleading, as there are actually under 300 rows of data for this figure.

The best thing to do with the worksheet created is to delete it when you've finished having a look at it:

The Sheet1 worksheet

This worksheet won't be dynamically updated when you refresh the underlying pivot table.

 

For normal pivot tables drill-down works well, but for pivot tables based on tabular models the results aren't as easy to interpret.

Quick Explore

This feature is useful, if initially confusing.  Suppose that you have the following pivot table:

Simple pivot table

The pivot table is already showing quantity broken down by centre type.

 

Suppose that the pivot table field list looks like this:

The pivot table field list

The other dimensions to this pivot table not currently displayed are Region and Species, so you can explore by these.

 

When you right-click on a value in the pivot table, you can choose to quick explore it:

Quick explore option

Here we're exploring the figure 4806.

You can now explore by any of the fields which are available in the pivot table field list but not currently displayed as row or column headings in your pivot table:

Drilling down to species

You can choose to explore in this case either by region or by species.

 

If you choose Species (as above), Excel will move the currently displayed row labels (CentreType) to the filters section of the pivot table, and show the species (as chosen) down the left side instead:

The quick explore results

Since we clicked on a Retail Park cell, Excel displays this as the chosen centre type in the filter section of the pivot table.

 

You could now repeat this trick to look at the sales of birds for retail parks in more detail:

Quick explore birds

Right-click on the cell whose contents you want to break down.

 

Because you can't see the centre type on the left any more, this is offered up as an option:

Drill down by region

Despite this, however, we'll choose to break this figure down by region.

 

We now have two filter choices at the top of the pivot table - Retail Park and Birds:

Quick explore a second time

The figures shown sum to the total birds figure above of 1289.

 

Confused?  I usually am.  The idea's good, but the implementation takes a bit of getting used to!

We're getting near the end of this (long) tutorial now, but there's one important topic I haven't yet covered: slicers.  Time to remedy that!

This blog has 0 threads Add post