Microsoft SQL Server 2008 R2 Specifications download pdf (Page 219)

PowerPivot for Excel CHAPTER 10 199

Data Analysis Expressions

The ability to combine data from multiple sources into a single PivotTable is amazingly pow-

erful, but you can create even more powerful reports by enriching the PowerPivot data with

Data Analysis Expressions (DAX) to add custom aggregations, calculations, and lters to your

report. DAX is a new expression language for use with PowerPivot for Excel. DAX formulas

are similar to Excel formulas. However, rather than working with cells, ranges, or arrays as in

Excel, DAX works only with tables and columns. You can use DAX either to create calculated

columns or to create new measures.

Calculated Columns

A calculated column is the set of values resulting from an expression that you apply to a table

column or another calculated column. For example, you can concatenate values from two

separate columns to produce a single string value that displays in a third column. You can

also perform mathematical operations, manipulate strings, look up values in related tables, or

compare values to produce results in a calculated column. To add a calculated column, click

an empty cell under the Add Column column heading and type an expression in the formula

bar. In your report, you can use the new calculated column just like any other column from

your PowerPivot data. An expression that calculates gross prot looks like this:

=[Sales Amount]-[Total Product Cost]

Measures

A measure is a dynamic calculation that is displayed in the value area of the PivotTable. Its

value depends on the current selection of items in rows and columns and in the report lter.

A measure differs from a calculated column in that the calculated column values persist in

the PowerPivot data whereas the measure values calculate at query time and do not persist in

the data store. The calculated column values are scalar, and the measure values are aggre-

gates. Last, a calculated column may contain string values or numeric values, but a measure is

always a numeric value.

As an example, consider a calculated column that shows gross prot. The PowerPivot

table would include a gross prot value for each sales transaction, which a PivotTable can

later aggregate. However, if you create a calculated column to store a gross prot margin

percentage value, the aggregate in the PivotTable will not be correct because percentage

values are not additive.

To create a measure, you must rst create a PivotTable or PivotChart. In the Excel window,

select the PivotTable or PivotChart, and then click the New Measure button on the Power-

Pivot tab of the ribbon. You then provide a name for the measure for all PivotTables in the

1 2 ... 214 215 216 217 218 219 220 221 222 223 224 ... 235 236

Microsoft SQL Server 2008 R2 Specifications Page 219