Microsoft Office Tutorials and References

In Depth Information

Adding Measures

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-

Measures.xlsx.

FIguRE 2-22
The context menu with which you add a new measure to the PowerPivot model underlying

the PivotTable.

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.