• Technology
  • CFO.com | US

Spreadsheets: Mission Impossible Completed with PowerPivot

The makers of PowerPivot should be nominated for a Nobel prize, quips Jelen. Why? For one thing, PowerPivot will wrest the power of business intelligence from the IT department and put it in the hands of accounting and finance.

In PowerPivot, Calculated Fields are Called Measures

In the world of PowerPivot, the calculated field feature is replaced with something called a Measure. Measures are created in a formula language called DAX (Data Analysis eXpressions). DAX offers many of the same functions as in Excel, but offers 80 incredibly powerful new functions.

The DAX language can be used to calculate new columns in the PowerPivot grid, or to calculate new fields in the resulting pivot table. For instance, say that you have a 100-million-row dataset that is summarized in a 9-row pivot table. If you create a calculation in the grid, PowerPivot has to do the calculation a 100 million times. If you create a calculation in the pivot table, PowerPivot only calculates the 9 cells in the final pivot table.

Kelly’s pivot table will require three measures.

12. In Excel, go to the PowerPivot tab of the ribbon. Click New Measure.
13. In the Measure Settings dialog, you have to choose a base table name. (Remember that PowerPivot is able to create pivot tables from multiple worksheets). In this case, there is only one dataset, so leave the Table Name as Table1.
14. Select a name for the field such as MinPrice or MinP.
15. The formula is =MINX(Table1, Table1[Price]), as shown in Figure 9. After typing the formula, you can click Check Formula to make sure you typed the formula correctly. Note that MINX is one of the new functions introduced by PowerPivot.

Fig. 9

MrExcel 4-13Fig09-B.gif

 

16. Click OK. The minimum price for each product will appear in the pivot table. Steps 12-16 are slightly longer than building a Minimum price in a regular pivot table. However, the payoff occurs in step 18.
17. Repeat steps 12-16 to calculate the Max Price. The formula is MAXX(Table1, Table1[Price])

Repeat steps 12-16 to calculate the Range. DAX Measures are allowed to refer to any existing DAX Measures, so the formula is simply: =Table1[MaxP]-Table1[MinP] (Figure 10). By the way, use the AutoComplete function as you type. As soon as you type =T, you can select Table1[MaxP] from the dropdown without having to type the entire syntax. You will now have a pivot table showing the min, max, and range, as shown in Figure 11.

If you add new data to the original dataset, there will be one extra step to refresh the pivot table.
1. On the PowerPivot tab, click Update All
2.On the PivotTable Tools Option tab, click Refresh.

Fig. 10

MrExcel 4-13Fig10

 

Fig. 11

MrExcel 4-13Fig11

 

Discuss

Your email address will not be published. Required fields are marked *