Microsoft Office Tutorials and References

In Depth Information

FIguRE 8-30
The merged table shows information coming from both OLAP and Excel.

Once again, the real power of PowerPivot remains in the capability to integrate data coming

from different sources. As simple as it is, the Paste Append option lets you build complex

forecasting scenarios.

Defining Sets

Let us continue our coverage of advanced Excel features by analyzing another useful option

of Excel 2010â€”the ability to define sets. Like many of the other functions described in this

chapter, this feature is tied not only to PowerPivot but is an Excel feature that works on any

PivotTable type. Before we go on speaking about how sets work, let us explain what a set is.

Intuitively, a set is a list of items, where
item
might be anything we would like to think about. You

can define a set by enumerating the items in it, as you normally do with your shopping list, where

each item is listed separately. Or you can define a set by giving a description of what belongs to

it, such as the set of all countries in North America. Both are good definitions of a set.

Nevertheless, because this is a PowerPivot topic, we need to understand the meaning that

PowerPivot gives to a set. A PivotTable is made up of cells, and each cell is defined by the

filter context that determines which rows to take from the underlying PowerPivot tables to

compute its value.

So you might think that each cell defines a filter context. The contrary is true as well: a filter

context defines a cell. Moreover, if you move a step forward, you might think of a filter context

as something that defines the set of rows that need to be accessed so that you can compute

the formula that gives a value to a cell.