Understanding Measure Calculation

Quick reference

• 00:04 As cool as it is to be able to write a measure inside Power BI,
• 00:08 it's more important to actually understand how these measures are actually evaluated.
• 00:15 Every single measure, every single data point that you see on a visual,
• 00:19 it's important to understand is evaluated against the raw source tables.
• 00:24 And this is a key thing to recognize.
• 00:27 Every single data point is evaluated independently of
• 00:30 all of the others that you see on the visual.
• 00:33 What this means is, if you have a table that shows a total on row one, and
• 00:37 a total on row two, and down the bottom, it's got a grand total,
• 00:41 that grand total is not actually the sum of the things above.
• 00:45 It reaches back to the raw table,
• 00:47 and is evaluated independently of the items above it in the visual.
• 00:51 And that's a little bit of a confusing concept to understand.
• 00:54 And that's why we're really gonna focus here on how these measures actually
• 00:58 calculate.
• 01:00 The way that it's done is that every single data point
• 01:04 actually reaches back into the raw source table for the model.
• 01:09 It then applies something called the filter context to those tables.
• 01:13 And basically what this means is it looks at all of the other visuals and
• 01:17 what's been filtered upon.
• 01:18 So if you selected a specific thing in another visual that becomes part of
• 01:22 the filter context.
• 01:23 That filter context generally reduces the number of
• 01:26 rows that are available in the raw source table,
• 01:28 cuz it says, hey these ones are no longer relevant, let's get rid of them.
• 01:33 Once it's finished applying the filtered context and
• 01:35 getting rid of the rows that are no longer relevant,
• 01:37 it then performs the arithmetic that's in your measure pattern, the sum function or
• 01:42 count function whatever you wrote on whatever rows are left.
• 01:46 It returns that aggregation to the actual visual for that specific data point.
• 01:52 And then it moves on to the next data point and it starts all over again.
• 01:57 This means that understanding and
• 01:59 controlling filter context is the key to mastery of formulas inside Power BI.
• 02:05 So this is one of the reasons why we're really focusing on
• 02:08 this particular piece right here, and
• 02:10 why we're gonna drill it home with one more really detailed example here.
• 02:15 So let's look at figuring out what filter context is, and how it actually applies?
• 02:20 We're gonna do that in the context of this particular table.
• 02:23 Notice we've got temperature range down the left hand side, we have a column
• 02:27 called July, and then the body area of the table, we have some values.
• 02:31 Those values are based on the measure pattern called Avg Daily Sales,
• 02:36 which is equal to the average of sales amount.
• 02:39 So we're taking the sales amount column, were averaging
• 02:43 all of the values that are applicable, and we're returning it into a specific cell.
• 02:48 Specifically right now, we're gonna focus on this one here that shows 177.13,
• 02:53 how do we actually come to that number?
• 02:56 Well, as it happens, there's a couple of more things in play here, too.
• 02:59 We have a slicer.
• 03:01 Now a slicer is a very special kind of visual that layers
• 03:04 on some more filter context.
• 03:06 It's one of the very odd ones that can actually be used with other visuals to
• 03:10 make a more complex filter context.
• 03:13 We also have this little chart over here which is called Amount by Account Group.
• 03:17 And you'll notice that revenues is selected.
• 03:19 So the big question then is what makes up the filter context
• 03:24 in this particular case?
• 03:26 The first thing is that we can apply the filter context from the year.
• 03:30 It's been selected as 2009.
• 03:32 Now in most visuals when we select something,
• 03:34 it clears the filter context from all of the other visuals.
• 03:37 In the case of a slicer, it sticks around and layers on, so
• 03:39 that's something we gotta be a little bit aware of here.
• 03:42 The second thing that contributes, 10 to 15 from the temperature range.
• 03:47 This is a specific value from a specific column, called temperature range,
• 03:50 that adds to the filter context.
• 03:54 We also have July from the month short call
• 03:57 that also adds to the filter context as well as the revenues value that
• 04:02 has been selected in the amount by account group column.
• 04:05 So the filter context is made up of year = 2009, and to the temperature range is ten
• 04:10 to fifteen, and month short equals July, and the account group equals revenue.
• 04:15 That is our complete and consolidated filter context.
• 04:19 What we then do is we actually apply the filter context to the underlying source
• 04:23 tables.
• 04:24 This filters down and reduces the number of rows that are going to be used
• 04:28 when calculating the particular measures.
• 04:31 So we get rid of all of the records that are not relevant,
• 04:34 all the stuff that we didn't select here.
• 04:37 And then we perform the measures arithmetic on whatever is left over and
• 04:40 return that to the visual.
• 04:41 So in this case, we get rid of all the rows that don't equal 2009.
• 04:45 We get rid of all of the rows where temperature range doesn't equal 10 to 15.
• 04:48 We get rid of all the rows where month short doesn't equal July, and
• 04:51 account group doesn't equal revenue.
• 04:53 Whatever rows are left, we then take the amount column, apply the average to it,
• 04:58 and return that to the cell.
• 04:59 And then the next thing is for the 226.72,
• 05:03 we start all over again from raw unfiltered tables.
• 05:07 It starts the entire process over.
• 05:09 So that happens for the 226.72, it happens for the 317, it happens for the 185.
• 05:14 And most importantly, when it gets to the total it does the same thing.
• 05:18 The only difference on the total row is that there's no filter context for
• 05:22 temperature range because all of the values are used in that particular
• 05:26 one, okay?
• 05:26 So that's the big difference here.
• 05:29 When you get into debugging measures and things aren't working, follow this chart.
• 05:33 This is really useful to understand.
• 05:35 If you can work out what the filter context is and how the process goes,
• 05:39 you will be able to debug any measure that uses a basic measure pattern.

Lesson notes are only available for subscribers.