How to understand implicit versus explicit measures in Excel Power Pivot

Although PivotTables in Power Pivot may appear similar to those in classic Excel, critical differences exist. Creating measures is one area where Power Pivot diverges from traditional Excel, as we have the option to use implicit or explicit measures. This post explores the advantages and disadvantages of implicit and explicit measures in Power Pivot:

This Data Model consisting of three employee record tables has already been loaded into Power Pivot as a PivotTable.

Summarizing data, the classic PivotTable way

Other than being able to retrieve data from multiple tables, making calculations in Power Pivot PivotTables appears very much like those in classic Excel. For example, we could drag dept_title to Rows and Sum of sal to Values to find the total payroll for each department:

To change the total salary to an average, you could change Sum of sal to Average of sal through Value Field Settings, just like you might with a classic PivotTable:

As familiar and intuitive as it is to create measures this way with classic PivotTables, Power Pivot offers a sturdier alternative. Let’s take a look.

The issue with implicit measures

To see how the Data Model processes these calculations, head to the Power Pivot tab on the ribbon, and then click on Manage Data Model. Next, click Diagram View under View on the Home tab of the ribbon. Under the Advanced tab of the ribbon, enable Show Implicit Measures. Two entries will be added to the emp table:

These two measures are the ones you created via the PivotTable earlier. Referred to as implicit measures, they are automatically generated by Power Pivot. Implicit measures are easy to create and facilitate quick data exploration and analysis without extensive calculation development.

However, they lack customization and reusability across the Data Model. Moreover, their “hidden” nature makes them challenging to validate. Given these constraints, go ahead and right-click on these measures in the Data Model now to delete them:

Let’s take a look at the alternative: explicit measures.

Creating explicit measures

To create an explicit measure, exit the Power Pivot editor and return to the main Excel interface. Then, go to the Power Pivot tab on the ribbon, select Measures, and choose New Measure.

Now, let’s create a Total salary measure that sums up the sal column of the emp table. We can achieve this by using a simple DAX calculation, which should be familiar syntax to those who have worked with Excel tables. Additionally, we’ll associate this measure with the emp table by specifying the Table name and format it as currency with zero decimal places.

Click OK and you will see that this measure is now available in your PivotTable Fields:

As an explicit measure, Total salary is now fully transparent and editable within the Data Model. For instance, you can easily edit it by right-clicking on it in the PivotTable Fields menu:

Creating multiple explicit measures

For practice, go ahead and create one more measure called Total hours. Your calculation should look like this:

Calculated measures truly shine when used as inputs for other measures, allowing for advanced and auditable calculations beyond the capabilities of implicit measures.

For instance, we can calculate an hourly rate by leveraging Total salary and Total hours as follows:

You can download a completed workbook of this model here:

Explicit measures are the way

Although implicit measures offer convenience, explicit measures provide transparency, customization, and the ability to create sophisticated calculations. It’s worth the extra effort, and I encourage you to make all of your Power Pivot measures explicit, no matter how simple. By embracing explicit measures, you will unlock the full potential of Power Pivot for robust and auditable data analysis.

A table comparing implicit versus explicit measures follows:

What questions do you have about implicit versus explicit measures in Power Pivot, or about Power Pivot measures more generally? Let me know in the comments.