Excel 2019 Bible

817

C H A P T E R

37

Adding Formulas to Power Pivot

IN THIS CHAPTER

Creating your own calculated columns

Utilizing DAX to create calculated columns

Creating calculated measures

Using Cube functions to break out of PivotTables

W

hen analyzing data with Power Pivot, you’ll often find that you need to expand your analy-

sis to include data based on calculations that are not in your original data set. Power Pivot

has a robust set of functions called data analysis expressions, or DAX functions, which allow

you to perform mathematical operations, recursive calculations, data lookups, and much more.

This chapter introduces you to DAX functions, and it provides the ground rules for building your

own calculations in Power Pivot data models.

Most of the examples in this chapter are available on this book’s website at http://www.wiley.com/go/

excel2019bible. The filename is Power Pivot Formulas.xlsx.

Enhancing Power Pivot Data with Calculated Columns

Calculated columns are columns that you create to enhance a Power Pivot table with your own for-

mulas. Calculated columns are entered directly in the Power Pivot window, becoming part of the

source data you use to feed your PivotTable. Calculated columns work at the row level; that is, the

formulas that you create in a calculated column perform their operations based on the data in each

individual row. For example, imagine you have a Revenue column and a Cost column in your Power

Pivot table. You could create a new column that calculated [Revenue] minus [Cost]. This calcu-

lation is simple and is valid for each row in the data set.

Calculated measures are used to perform more complex calculations that work on an aggregation of

data. These calculations are applied directly to your PivotTable, creating a sort of virtual column

that can’t be seen in the Power Pivot window. Calculated measures are needed when you need to

calculate based on an aggregated grouping of rows, such as the sum of [Year2] minus the sum of

[Year1].

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.