In the first part of the series, we looked at the use of Data Analysis Expressions (DAX) to add calculated values to each row of a Power Pivot, or Power BI, data table. We used the MAXX() and RELATEDTABLE() functions to show how DAX calculations can work with entire tables of data rather than just individual values. Adding a calculation to each row of a table in the Power Pivot window can be inefficient for tables with large numbers of rows. There is another way to use DAX to add calculations to a PivotTable based on Power Pivot.

Like Calculated Fields in ‘normal’ PivotTables, Measures can be added directly to the PivotTable report and are only calculated for each calculated cell displayed in the PivotTable. Note that Microsoft has changed the title of measures over recent versions of Excel.

They were introduced in Excel 2010 as Measures. Then, in Excel 2013, they were called Calculated Fields, creating the possibility of confusion with PivotTable Calculated fields. From Excel 2016 onwards the term Measures was re-instated.

Adding a measure

Measures can be added via the Power Pivot window, but it is probably easier to use the Measures command in the Calculations group of the Power Pivot Ribbon tab. Here we have clicked on the Measures command and chosen New Measure… from the dropdown (see Figure 1, below).

This example is a very simple Measure. We are just creating a total of the Quantity field in the Order Details table. In fact, we could have achieved a similar result just by adding the Quantity field to the Values area of our PivotTable report. However, using the Measure dialog has several advantages. We can immediately give our measure a meaningful name rather than leaving Excel to create the normal ‘Sum of …’ name; we can add an informative description and we can set the format for the measure to be used wherever we include it. Of course, we do need to create our Measure formula manually rather than leaving Excel to do this for us, but AutoComplete helps by listing the function arguments required and the fields that we can use (see Figure 2, below).

It is always a good idea to use the Check formula button to make sure the syntax of your expression is correct before you try and accept it. Note also that Measures need to be associated with tables. The Measure will be included in the list of fields for the chosen table in the PivotTable Fields pane.

Filtering

When working with Measures, it helps to understand how a cell value in the Values area of a PivotTable is created. In effect, each value is the result of applying some sort of aggregate function to an entire column of values that has usually been filtered in some way. The field that is added to the Values area defines which table column is to be filtered and aggregated.

The other components of the PivotTable all act as filters applied to each cell in the Values area. The way that the explicit Filters and Slicers work is obvious enough, but the Rows and Columns fields also act as filters. For example, you can think of adding a field to the Rows area as filtering the rows in the value column to only include those rows where the value for the Rows field in the table is equal to the value displayed on that row of the Rows area. Where multiple fields are added to the Rows and Columns areas, they just apply additional filter criteria.

Returning to our simple sum measure, we are summing the Quantity column of the Order Details table. When we add our measure to the Values area of a PivotTable, each cell is filtered as described above to create our individual totals.

Advanced calculations

The concept of filtering is vital when working with measures since manipulating the filters gives you the power to create all sorts of more advanced calculations. In a rather dramatic increase in complexity, we have added a measure to calculate the percentage of each individual value to the total quantity sold:

=DIVIDE([Total Quantity], CALCULATE([Total Quantity],ALL(Categories)))

The three DAX functions used here work as follows:

• DIVIDE() helps avoid ‘division by zero’ errors. As well as specifying a numerator and a denominator, you can provide an optional value to be used when the denominator is zero.
• >CALCULATE() applies one or more filters to an expression, adding to, or overriding the filters that the PivotTable itself applies.
• ALL() allows you to remove some or all of the PivotTable filters.

Our expression shows how measures can be used in other measures, with the value of our [Total Quantity] measure being divided by the value of our [Total Quantity] measure calculated with any filters from the Categories table being removed.

So, our numerator respects the filter applied by our Rows area but our denominator has this filter overridden by ALL() and thus returns the grand total, allowing our percentage to be calculated for each row (see Figure 3, below).

Conclusion

This has been a very brief introduction to some of the capabilities of using DAX to create measures in a PivotTable. In the next part of the series we are going to look at a very specific category of DAX functions: the ‘Time Intelligence’ functions that enable the creation of calculations based on periods and comparisons between periods.