Power BI – The Data Model – An Introduction to Measures

In this lesson we’re going to start looking at Measures and in my view this is where Power BI really starts to come into its own..

So to start off with, what is a measure?

So the best way to start off thinking about a measure is that it’s an aggregating calculation or a formula that you can add into a pivot table, powerpivot table or into Power BI.

Unlike a calculated column which is also a calculation that you add into a dataset, a measure does not create additional data which a calculated column does.

A good way to think of it is that calculated columns create datasets which measures can then summarise. Excel then uses the measures to create pivot tables or Power BI uses the measures to create visualisations.

A bit more detail I think here and a few examples would be useful. Let’s go back once again to the humble pivot table and back into the dataset that we used for looking at to understand filter context.

Ok here we are we’re back in the workbook and we’re looking at a pivot table over the Bromley by Bow dataset. Ok first I’m going to put SKU numbers or ItemID on rows and then put Sales units in Values. There we go we’ve now got the total units sold by SKU number or ItemID number in Bromley by Bow in this column marked Sum of 2018 Sales Units.

Now this column here doesn’t mean that we’ve created a new column showing the sales by SKU in the original dataset like we would with a calculated column. After all in a standard dataset, how would that even work – after all, the pivot table calculates a different value for each individual SKU? What’s happened is that we’ve taken the dataset and then summarised the 2018 Sales Units column by SKU number.

This is called an implicit measure and Pivot tables automatically calculate implicit measures whenever we put a number field into the Values section on a pivot table. Each cell in column B is the equivalent of a SUMIF expression calculating the total units sold in 2018 if the SKU number equals the adjacent cell on the table. Here we go, if I ungroup this column then we can see that I’ve put a SUMIF calculation in every cell next to the pivot table, calculating the sum of units, based on the SKU Value in the pivot table and in every case the number is the same.

It’s called an implicit measure because pivot tables assume that by putting a field into the value pane of the pivot table you want the pivot table to do some sort of aggregation calculation using the data filtered by the filter context of the fields that are in rows and in columns and of course you can change the aggregation function here by changing the aggregation function type. However we are restricted to these aggregation functions that are here in this section. We can’t customise these calculations saying for example, I only want to sum those sales made on a weekend, or I want to sum those sales made during the Christmas period. You could identify those sales numbers of course through filters, but then what if you wanted to take that number to create a calculation for example, sales made at Christmas as a proportion of overall sales? You certainly couldn’t do that in a pivot table using implicit measures.

So let’s flip back to our Power BI file. Here I’ve put ItemID in a table visualisation and now I’ll put sales made at standard price into the visualisation as well. Power BI hasn’t put each row of the Retail Analysis dataset into the table, no it has summed the units sold of each type of Item and presented it in the table. It has created an implicit measure, summing the sales units in the filter context of each individual Item ID number. And we didn’t ask Power BI to calculate the total sales by ItemID, it just created the implicit measure automatically because the two fields that we put up here in the Values section were ItemID and Units at standard price. Power BI automatically aggregates the Sales units column, but then performs a different calculation for each row depending on the filter context i.e. depending on which ItemID number is in the same row as the calculation. And we can change this implicit measure just as we can in Excel’s pivot tables by selecting the downward arrow over here in the Units At Standard Price tab and selecting another aggregation option

 If I changed ItemId to DistrictName then the implicit measure being essentially SUM(Unit Sales) would stay the same but the calculations would change of course because now the filter context is not ItemID but District Name. Now Power BI looks through the dataset, filters the dataset by the different department present on each row and then performs a SUM of Unit Sales on each of those rows.

Now in Power BI (and Power Pivot for that matter), instead of having to rely on the implicit measure options PBI gives us here,  we can create these measures, we can create customisable calculations that you can then use in additional calculations. These are called Explicit measures.

For example I could create a measure totalling total sales made in January or February or any other month and then a calculation of total sales for the whole year and then express monthly sales as a percentage of overall sales. And of course because these measures are dynamic, Power BI will work out the result of the measure regardless of what other data is in the visualisation!

Lesson takeways

a) Measures are aggregation calculations such as SUM, COUNT or AVERAGE that are performed over columns of data and respond to filter context.

b) Implicit measures are measures calculated automatically by pivot tables and Power BI visualisations

c) Explicit measures can be customised to include or exclude different data.

d) Explicit measures can be used in subsequent measures but implicit measures cannot be.