Power BI – The Data Model – Explicit Measures

Hi there now in the last lesson we introduced the concept of measures and looked particularly at implicit measures. Implicit measures are aggregating calculations that Power BI and pivot tables automatically add to a table or visualtilsation to summarise values depending on the filter context.

In this lesson we’re going to introduce the concept of explicit measure.

 An explicit measure is a bespoke aggregating calculation that the user can define and add into a pivot table, powerpivot table or Power BI.

The measure can be defined to filter the available information to only include a subset of information, and then to perform an aggregate calculation on that subset.

Explicit measures can then be used in the calculation of other measures. This is the concept of derivative measures.

However, before we get onto the idea of creating measures that filter the available information to calculate values of subsets of the data, we need to go back to the beginning and look at the key available aggregate functions in Power BI.

Now for most of you who have been using Excel for a number of years should be comfortable with this slide as it gives the definition of an aggregate function and gives the key aggregate functions, all except one of which are in Excel.

Aggregate functions are functions that summarise multiple values into a single (scalar)

The key examples of aggregate functions therefore are as follows:

Missing Aggregation Table

Now as all measures are as their essence an aggregate factor, we should get some practice in at using these functions in Power BI, so we’re going to use the rest of this lesson to do that.

Switch to data view of Power BI.

Now some way down the line when you’re creating your amazing Power BI models, you might get to a point where you have hundreds and hundreds of measures and when that happens it can be a little difficult to remember which data table you stored the measure in.

A good rule of thumb can be to always store measures in the table to which the aggregating part of a function relates. For example If I wanted to create a sum of the value of 2018 Units sold at standar price, then I would create a new measure here in the 2018 table.

However an alternative method is to create a table where certain types of measures can be stored. We can do this by clicking on Enter Data. And then changing the name of the table to how we would like to organise the functions. So for example, I’m going to call this table, Base Aggregate Measures and then press Load.

There, we go, we’ve now got a place to store our Base Aggregate measures. Ok now the first thing we’re going to do is create a simple SUM calculation over the 2018 Units sold at standard price. To do that, I’ll right click on the Base Aggregate Measure table, and then select “New Measure”. Now up comes the formula bar and we’ve got the word “Measure” highlighted and then an equals sign. So first thing to do then is name the measure which I’ll call ‘2018 Sales at standard price’. Then after the equals sign, I’ll type SUM, then open the brackets. We need the name of the table here as the measure relates to a column in a table which is not the Base Aggregate Measure table (it’s the 2018 Retail Analysis table) so I’ll put in an apostrophe and up comes intellisense with my available columns and tables and I’ll select the 2018 Retail Analysis table by pressing tab. I’ll then type in an opening square bracket and a U and select Units at Standard Price by pressing tab again. I’ll then close the brackets. Now I’ll do the same with the 2019 Sales at Standard price. I’ll select New Measure, then put in the name ‘2019 Sales at standard price’. Then after the equals sign I’ll put in an apostrophe to bring up the table options, and type in 2019 and select the 2109 Retail Analysis table using tab. I’ll then open a square bracket and put in “2019U” and then press tab and close the round brackets.

There we go two new explicit measures. Let’s go and have a look at them in the report view.

Switch to report view

Ok here we are back in the report view, and let’s put in a table with DistrictName from the Store table on rows and first put in the field Units at Standard price from the 2018 Retail Analysis table. There we go. Now let’s go and put in the new 2018 Sales Standard explicit measure in our Base Aggregate Measure table – which look has a little calculator icon to denote that it’s an explicit measure. Ok there we go, now the two columns are exactly the same. And that of course makes sense because our explicit measure is exactly the same as the implicit measure that Power BI calculates automatically. Both are just a simple SUM function evaluated line by line in the DistrictName filter context.  And so what you may think!

Well let’s put in the Units sold at Standard price from the 2019 Retail Analysis table and the new 2019 Explicit measure that we’ve calculated. And of course these two columns are also the same.

Right well let’s spice this up a bit and ask Power BI to calculate the variance between the 2018 and 2019 sales. Now you can’t do that with the implicit measures but you can do it with the explicit measures.

I’ll go to the Base Aggregate Measures table, select New Measure and call this measure 2019 vs 2018 Sales at Standard price variance (units). After the equals sign I’ll put in the apostrophe and a B and then from intellisense I’ll select the 2019 sales measure and press tab. I’ll then put in a minus, then an apostrophe and select the 2018 Sales measure and press tab and press return to confirm the formula definition. Now this measure uses two previously defined base measures and is therefore called a derivative measure.

Here’s our new measure in the Base Aggregate Measure table so let’s pop that in our visualisation. And there we go, we can now see the difference in our sales by district year over year! Let’s just format the numbers a little to make it a little easier to read. I’ll select the 2018 Sales units measure in the fields pane, go to the modelling tab and then press the comma button. We can see now that the formatting of our table for this measure has changed, and that looks better, so I’ll do the same for my other two explicit measures.

Now let’s get rid of our implicit measures here.

Of course because this table is essentially a pivot table these explicit measures are dynamic which means that if we put on a new filter context, for example we take off district name and put on County name, then measures will adapt and calculate the total sum of units sold in the filter context of each county.

 Now we could also include a percentage change on here too so let’s create a measure for that.     

I’ll go to the Base Aggregate Measures table, select New Measure and call this measure 2019 vs 2018 Sales at Standard price variance (%). Now you can do the following formula as you would in Excel which has the numerator over the denominator.   After the equals sign I’ll put in the apostrophe and a B and then from intellisense I’ll select the 2019 vs 2018 variance measure and press tab. I’ll then put in a forward slash , then an apostrophe and select the 2018 Sales units measure and press tab and press return to confirm the formula definition.

However if any of the fields in our table are blank or have a null when the calculation is evaluated, this will create an unexpected value in our results. Let’s put the measure in our table and then format the column as a percentage to show this. To do that. I’ll select the measure in the fields pane and then go to the modelling tab and select the percentage button. Ok if I scroll down to North Yorkshire and Northamptonshire, we can see that the we have value “Inifity” instead of a value, and this is because this county didn’t exist in 2018, so we have a blank in the 2018 column.

We can correct this however. I’ll go back into the measure by selecting it in the fields pane, then directly after the equals sign, I’ll put in  the word DIVIDE and open the brackets. I’ll then replace the forward slash with a comma, and then after the denominator, this function gives me the option to put in An alternate result if Power BI can’t calculate the value as it expects to. Now you could either put in a text value such as “New Store”, or you could put in 1 if you wanted the result to be 100%. But either way the grand total at the bottom does not change.

So there we go, a first look at explicit measures using aggregate functions. So what have we learned from this lesson? (The answer is a lot!!).