Excel 2019 Bible

Chapter 37: Adding Formulas to Power Pivot

37

Clicking the Delete button will activate a message box asking you to confirm that you want

to remove the measure. After confirming with Yes, the calculated measure will be removed.

Using Cube Functions to Free Your Data

Cube functions are Excel functions that can be used to access the data in a Power Pivot

data model outside the constraints of a PivotTable. Although Cube functions are techni-

cally not used to create calculations themselves, they can be used to free Power Pivot

data so that it can be used with formulas that you may have in other parts of your Excel

spreadsheet.

One of the easiest ways to start exploring Cube functions is to allow Excel to convert your

PivotTable into Cube functions. The idea is to tell Excel to replace all cells in the PivotTable

with a formula that connects back to the Power Pivot data model.

Follow these steps to create your first set of Cube functions:

1. Start with a PivotTable created from a Power Pivot model.

The sample file for this chapter contains a tab called Cube Functions with a PivotTable already created.

2. Select any cell inside the PivotTable and then select PivotTable Tools Analyze
   ➪ OLAP Tools ➪ Convert to Formulas, as demonstrated in Figure 37.21.

FIGURE 37.21

Select the Convert to Formulas option to convert your PivotTable to Cube formulas.

After a second or two, the cells that used to house a PivotTable are now homes for

Cube formulas. The Formula bar shown in Figure 37.22 illustrates a Cube function.