MrExcel.com - Your One Stop for Excel Tips & Solutions

Adding Calculations In the Power Pivot Grid

March 17, 2023 - by

Adding Calculations In the Power Pivot Grid

Power Pivot introduces a new formula language called Data Analysis eXpressions or DAX. When you see a Power Pivot demo, the 2 million rows and the joining tables look impressive. But it turns out that DAX is the really jewel in Power Pivot.

DAX is used in two places. DAX is used to add new columns in the Power Pivot grid. When used in this way, it is 99% similar to the functions that you know and love in regular Excel. The real power in DAX is when you add new calculated fields to the resulting pivot table.

While I am going to cover some essential DAX examples here, the my friend Rob Collie from Power PivotPro.com has written the essential title on understanding DAX. His book is Power Pivot and Power BI.

DAX shares 81 functions with Excel, so if you are proficient with Excel functions, you should have little problem working in DAX. There are two functions in DAX that differ from the equivalent function in Excel - FORMAT replaces TEXT and RELATED replaces VLOOKUP.

The first blank column in the Power Pivot window is called Add Column. Click in any cell in that column. Type an equals sign and enter your formula. For example, type =YEAR( and then, using the mouse, click on the Year field. Type a closing parentheses and press Enter. Your formula will populate all the way down the grid.

Right-click on the heading, choose Rename, and type a meaningful field name such as Year.

Seems easy, right? The function is the same as in Excel. Now - there are 81 functions you can use here, and 79 of them are identical to Excel. One that is different: The TEXT function in Excel is FORMAT in DAX. So, in the image below, the month name uses FORMAT.

To get the Month Abbreviation in the Power Pivot grid, the formula is =FORMAT([Date],"MMM"). This is in contrast to Excel, where you would use =TEXT(A2,"MMM").
Figure 1058. DAX uses FORMAT instead of TEXT

It turns out that FORMAT offers choices for Quarter and Week, thus is more robust than TEXT.

Gotcha: each column can have only one formula. Every row in the column has to have an identical formula. You cannot refer to cells in other rows. Therefore, the concept of cell addresses like A2 is not relevant in Power Pivot.

Gotcha: As you are building your formula, you can click a field with the mouse to refer to that field. You cannot use the arrow keys to select a field.

This article is an excerpt from Power Excel With MrExcel

Title photo by Artturi Jalli on Unsplash