Power Pivot Principles: Measure Creation in Practice

Welcome back to our Power Pivot blog series.  Today we discuss proper practices we should adopt when creating measures.

This blog builds on what was mentioned in the previous blog Power Pivot Principles – Measures and Aggregation.

Before we create more complicated measures, it is good practice to convert each field that we are going to use into a measure.  There’s a good reason to do this, even if it seems like an unnecessary chore.  In Excel, if you add / remove rows or columns Excel’s formulae will update automatically.  However, if you change a field name in Power Pivot, the dependent measures will not update and will not calculate as a consequence. 

Therefore, in order to avoid changing many measures when a field is renamed, it is simpler to create an aggregate measure of that field first and then have all further measures refer to that intermediate measure.  Therefore, this will save us a lot of time if we turn fields into ‘simple’ measures first.

Converting fields into measures first yields two benefits:

1. It saves time in the long run; we avoid having to use an aggregation function each time we reference the field
2. It simplifies the formula when creating complex measures. 

Let’s create a measure for the ‘Product Cost’.  In this example, we have completed the measure dialog box with the following details:

Next up, we’ll construct a similar ‘Sales’ measure:

We can now move on to modify the ‘Profit’ measure. From the ‘Measures’ drop-down menu, select ‘Manage Measures…’:

The ‘Manage Measures’ dialog box appears displaying all measures along with their formulae.  Select the Profit measure and then press ‘Edit’ button:

The measures dialog box appears allowing us to edit the formula.

We can now condense the formula down to:

See how complicate the new formula is?  Simple!

That’s it for this week, stay tuned to our blog page for more on Power Pivot.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.