Modern Data Analytics in Excel

Chapter 8. Creating Measures and KPIs in Power Pivot

In Chapter 7, the fundamentals of Power Pivot and the Data Model were introduced, including relationships, hierarchies, and calculated columns. With the Data Model in place, this chapter delves into creating DAX measures and KPIs to aid end users in data interpretation.

For demonstrations, refer to ch_08.xlsx in the ch_08 folder of the book’s companion repository. This chapter uses the same retail sales dataset from Chapter 7, with the Data Model predefined in the provided exercise file.

Creating DAX Measures

In Chapter 7, the attempt to add a profit margin column to the orders table led to an unsatisfactory result. For aggregating and recalculating results across different categories and time periods, DAX measures are necessary. In Power Pivot, measures can be created in two ways: implicitly and explicitly. To gain hands-on experience with these methods, proceed by inserting a PivotTable from the Data Model.

Creating Implicit Measures

To aggregate data, like finding the total order quantity by region, one would typically drag the fields directly into the PivotTable, as shown in Figure 8-1.

To adjust the aggregation to determine the average number of units sold by region, navigate to the dropdown on “Sum of Order Quantity” in the PivotTable. Then, proceed to “Value Field Settings” and, within the “Summarize value field by” section, switch from Sum to Average.

Figure 8-1. Classic drag-and-drop PivotTable aggregation

To see ...