Power Pivot Principles: Initial Measures – a Pro Tip

Welcome back to the Power Pivot Principles blog. This week, we explain the advantages of creating measures from fields. Who’d have thought..?

I can’t believe it’s taken 176 blogs to come up with this tip that we take for granted when building models in Power Pivot and Power BI. It’s deceptively simple but can avoid a lot of problems later.

Consider the following dataset:

This Excel Table, Example, contains just two fields, Sales and Costs. Let’s imagine we have imported this Table into Power Pivot in a usual way (e.g. from the ‘Power Pivot’, click on ‘Add to Data Model’) and we want to create a Gross Profit measure.

We could simply create the following:

(Don’t forget you need to aggregate fields, using DAX functions such as SUM, AVERAGE, MAX or MIN otherwise this measure will not resolve.)

This will work and we often see people build DAX formulae this way.  But we don’t.  In this example, we might create the following two measures first, where we make a measure out of each field we reference:

i.e. a measure called Total Sales,

and another called Total Costs:

Then, and only then, do we create our Gross Profit measure:

There are two advantages in creating these interim measures out of the reference fields:

1. Dependent formulae are easier to read
2. Should the field names in the source change you only need to change the definition of the original measure, created from the field.  You do not need to propagate this change throughout all measures that reference the field because they don’t.  This creates much less work (you have control over all measure names) and leaves a better audit trail too.

The simplest ideas are often the best.

That’s it for this week!


Come back next week to see how we use the disconnected table for performing a sensitivity analysis by staying tuned for our next post on Power Pivot in the Blog section.  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.