Microsoft Office Tutorials and References
In Depth Information
Even if you can perform many interesting calculations working at the row level of the tables,
some calculations cannot be defined at this level because they depend on the query context.
(In other words, they depend on the selection made by the user in the PivotTable.)
You will explore many of these calculations later in this topic (see Chapters 3, 6, 7, and 8), and
to do that, you need to learn the DAX language. But right now we want to show you a simple
example of the differences between a calculated column and a measure in PowerPivot. We
also briefly investigate why measures are sometimes needed.
You are going to implement a column that calculates the distinct count of products sold. A
distinct count computes the number of distinct values of a specific column and is very useful,
for example, for customers or products that happen to appear several times inside a table
such as SalesOrderDetails. This formula cannot be computed at the row level because, for
each sale, its value is 1 (one product sold) while, for many sales, its value is not the sum of all
the values at the row level. Instead, it needs to be computed based on user selection. Such
types of calculation cannot be defined at the row level, so they are called measures and need
to be defined at the PivotTable level.
To create a new measure, we need to right-click the PowerPivot Field List and choose Add
New Measure as in Figure 2-22. You can find this example in the companion file CH02-03-
FIguRE 2-22 The context menu with which you add a new measure to the PowerPivot model underlying
At this point, a new dialog box appears (see Figure 2-23) in which you need to provide the
new measure properties. Type a name for the new measure—say, DistinctProducts, and
then you need to write the DAX formula that calculates the value.