Creating your first calculated column

Creating a calculated column works very much like building formulas in an Excel table. Let's continue the walk-through with some sample data; follow these steps to create a calculated column:

1. Open the Pivot Formulas.xlsx sample file, activate the Power Pivot window (by clicking the Manage button on the Power Pivot Ribbon tab), and then select the Invoice Details tab.
2. In the table, you will see an empty column on the far right labeled Add Column. Click the first blank cell in that column.
3. In the Formula bar (see Figure 37.1), enter the following formula:

   =[UnitPrice]*[Quantity]

   

4. Press Enter to see your formula populate the entire column.
5. Power Pivot will automatically rename the column to Calculated Column 1. Double-click the column label, and rename the column Total Revenue.

Each calculated column that you create will automatically be available in any PivotTable connected to the Power Pivot data model. You don't have to take any action to get your calculated columns into the PivotTable. Figure 37.2 illustrates the Total Revenue calculated column in the PivotTable Fields list. These calculated columns can be used just as you would any other field in your PivotTable.

Formatting your calculated columns

You'll often need to change the formatting of your Power Pivot columns to match the data within them appropriately. For example, you might want to show numbers as currency, remove decimal places, or display dates in a certain way.

You are by no means limited to formatting just calculated columns. These steps can be used to format any column that you see in your Power Pivot window:

1. In the Power Pivot window, click the column that you want to format.
2. Go to the Home tab of the Power Pivot window and find the Formatting group (see Figure 37.3).

   

3. Use the option to alter the formatting of the column as you see fit.

Referencing calculated columns in other calculations

Like all calculations in Excel, Power Pivot allows you to reference a calculated column as a variable in another calculated column. Figure 37.4 illustrates this with a new calculated column called Gross Margin. Notice in the Formula bar that the calculation uses our previously created [Total Revenue] calculated column.

Hiding calculated columns from end users

Because calculated columns can reference each other, you can imagine creating columns simply as helper columns for other calculations. You may not want your end users to see these columns in your client tools. In this context, the term client tools refers to PivotTables, Power View dashboards, and Power Map.

Similar to hiding columns on an Excel worksheet, Power Pivot allows you to hide any column (it doesn't have to be a calculated column). To hide columns, simply select the columns you want hidden, right-click the selection, and then choose the Hide from Client Tools option (illustrated in Figure 37.5).

When a column is hidden, it will not show as an available selection in your PivotTable Fields list. However, if the column you are hiding is already part of the PivotTable report, meaning that you already dragged it onto the PivotTable, hiding the column in the Power Pivot window will not automatically remove it from the report. Hiding merely affects the ability to see the column in the PivotTable Fields list.

You'll note in Figure 37.6 that Power Pivot recolors columns based on their attributes. Hidden columns are subdued and grayed out, while calculated columns that are not hidden have a darker (black) header.

Identifying DAX functions safe for calculated columns

In the previous section, you used the Formula bar within the Power Pivot window to enter calculations. Next to that Formula bar, you may have noticed the Insert Function button labeled fx. This is similar to the Insert Function button found in Excel. Clicking this button will activate the Insert Function dialog box shown in Figure 37.7. This dialog box allows you to browse, search for, and insert the available DAX functions.

As you look through the list of DAX functions, you'll notice that many of them look like the Excel functions with which you are already familiar. Make no mistake, however; these are not Excel functions. Where Excel functions work with cells and ranges, these DAX functions are designed to work at the table and column levels.

To understand what that means, start a new calculated column on the Invoice Details tab. Click the Formula bar, and enter a good old SUM function: =SUM([Gross Margin]). Figure 37.8 shows the result.

As you can see, the SUM function sums the entire column. This is because Power Pivot and DAX are designed to work with tables and columns. Power Pivot has no construct for cells and ranges. It doesn't even have column letters on its grid. Where you would normally reference a range, as in an Excel SUM function, DAX basically takes the entire column.

The bottom line is not all DAX functions can be used with calculated columns. Because a calculated column evaluates at the row level, only DAX functions that evaluate single data points can be used in a calculated column.

A good rule of thumb is if the function requires an array or a range of cells as an argument, then it not viable in a calculated column. Therefore, functions such as SUM, MIN, MAX, AVERAGE, and COUNT don't work in calculated columns. Functions that require only single data point arguments work quite well in calculated columns, such as YEAR, MONTH, MID, LEFT, RIGHT, IF, and IFERROR.

Building DAX-driven calculated columns

To demonstrate the usefulness of employing a DAX function to enhance calculated columns, let's return to our walk-through example. Go to the Power Pivot window, and click the InvoiceHeader tab. If you accidentally closed the Power Pivot window, you can activate it by clicking the Manage button on the Home tab of the Power Pivot Ribbon.

The InvoiceHeader table, shown in Figure 37.9, contains an InvoiceDate column. Although this column is valuable in the raw table, the individual dates aren't convenient when analyzing the data with a PivotTable. It would be beneficial to have a column for Month and a column for Year. This way, you could aggregate and analyze your data by month and year.

For this endeavor, you use the YEAR(), MONTH(), and FORMAT() DAX functions to add some time dimensions to your data model. Follow these steps:

1. In the InvoiceHeader table, click the first blank cell in the empty column labeled Add Column on the far right.
2. In the Formula bar, type =YEAR([InvoiceDate]), and then press Enter.
3. Power Pivot will automatically rename the column to Calculated Column 1. Double-click the column label, and rename the column Year.
4. Starting the next column, click the first blank cell in the empty column labeled Add Column on the far right.
5. In the Formula bar, type =MONTH([InvoiceDate]) and then press Enter.
6. Power Pivot will automatically rename the column to Calculated Column 1. Double-click the column label, and rename the column Month.
7. Starting the next column, click the first blank cell in the empty column labeled Add Column on the far right.
8. In the Formula bar, type =FORMAT([InvoiceDate],"mmm") and then press Enter.
9. Power Pivot will automatically rename the column to Calculated Column 1. Double-click the column label and rename the column Month Name.

After going through these steps, you should have three new calculated columns similar to the ones shown in Figure 37.10.

As mentioned, creating calculated columns will automatically make them available through your PivotTable Fields list (see Figure 37.11).

Month sorting in Power Pivot–driven PivotTables

One of the more annoying things about Power Pivot is that it doesn't inherently know how to sort months. Unlike standard Excel, Power Pivot doesn't use the built-in custom lists that define the order of month names. When you create a calculated column like [Month Name] and place it into your PivotTable, Power Pivot will put those months in alphabetical order (see Figure 37.12).

The fix for this is fairly easy. Activate the Power Pivot window and select the Home tab. There you will find the Sort by Column button. Upon clicking the button, the Sort by Column dialog box shown in Figure 37.13 opens.

The idea is to select the column you want sorted and then select the column by which you want to sort. In this scenario, we want to sort Month Name by month.

Once you confirm the change, it will initially appear as though nothing happened. This is because the sort order that you defined is not for the Power Pivot window. The sort order is applied to your PivotTable. You can flip over to Excel and see the result in the PivotTable (see Figure 37.14).

Referencing fields from other tables

Sometimes the operation you are trying to perform with a calculated column requires you to utilize fields from other tables within the Power Pivot data model. For instance, you may need to account for a customer-specific discount amount from the Customers table (see Figure 37.15) when creating a calculated column in the InvoiceDetails table.

To accomplish this, you can use a DAX function called RELATED. Similar to VLOOKUP in standard Excel, the RELATED function allows you to look up values from one table to use them in another.

Take a moment to follow these steps to create a new calculated column that displays a discounted revenue for each transaction in the InvoiceDetails table:

1. In the InvoiceDetails table, click the first blank cell in the empty column labeled Add Column on the far right.
2. In the Formula bar, type the following:

       =RELATED(

   As soon as you enter the open parenthesis, a menu of available fields (shown in Figure 37.16) will be displayed. Note that the items in the list represent the table name followed by the field name in brackets. In this case, we're interested in the Customers[Discount Amount] field.

   

3. Double-click the Customers[Discount Amount] field and then press Enter.
4. Power Pivot will automatically rename the column to Calculated Column 1. Double-click the column label and rename the column Discount%.
5. Starting the next column, click the first blank cell in the empty column labeled Add Column on the far righ t.
6. In the Formula bar, type =[UnitPrice]*[Quantity]*(1-[Discount%]) and then press Enter.
7. Power Pivot will automatically rename the column to Calculated Column 1. Double-click the column label and rename the column Discounted Revenue.

The reward for your efforts will be a new column that uses the discount percent from the Customers table to calculate discounted revenue for each transaction. Figure 37.17 illustrates the new calculated column.

Nesting functions

In the previous example, you first created a Discount% column using the RELATED function and then used that column in another calculated column to calculate the discounted revenue.

It's important to note that you don't necessarily have to create multiple calculated columns to accomplish a task like this. You could, instead, nest the RELATED function into the discounted revenue calculation. This would be the syntax for the nested calculation:

=[UnitPrice]*[Quantity]*(1-RELATED(Customers[Discount Amount]))

As you can see, nesting simply means embedding desired functions within a calculation. In this case, instead of using the RELATED function in a separate Discount% field, you can embed it directly in your discounted revenue calculation.

Nesting functions can definitely save time and even improve performance in larger data models. On the other hand, complicated nested functions can be harder to read and understand.

Editing and deleting calculated measures

You may find that you need either to edit or to delete a calculated measure. You can do so by following these steps:

1. Click anywhere inside your PivotTable and then click the Power Pivot tab in the Excel Ribbon and select Measures ⇨ Manage Measures. This will open the Measure dialog box shown in Figure 37.20.

   

2. Select the target calculated measure, and click either the Edit or Delete button.

Clicking the Edit button will activate the Measure dialog box where you can make changes to the calculation setting.

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.