Understanding Measure Calculation

Quick reference

• 00:04 So now that you've seen the measure in action,
• 00:07 we need to understand how it actually works.
• 00:09 And the reason being, it doesn't work quite as you might expect.
• 00:14 In truth, every single measure is evaluated against the raw,
• 00:19 unfiltered source tables in the data model.
• 00:22 Every single measure, every cell you see in your pivot table,
• 00:26 is evaluated independently of every other cell.
• 00:29 This is a really important thing to understand because sometimes it's not
• 00:35 about what you might think you see, it's about this particular method of working.
• 00:40 The method that's used for measure calculation is this.
• 00:42 We take the raw unfiltered source table,
• 00:45 we apply filters to that table based on what's called the filter context.
• 00:50 That's gonna remove all of the rows that are not applicable to this
• 00:54 particular scenario.
• 00:56 And then,
• 00:56 we perform the measure's arithmetic on whatever rows are left behind.
• 01:02 This means that every single dax formula that we work with is about
• 01:05 the most important thing is to understand and
• 01:07 control that filter context to get the right number.
• 01:10 Now this is completely opposite what you might expect in Excel, because in Excel,
• 01:15 typically, we say, hey, I want to sum this specific range here.
• 01:20 Even if the table is 10,000 rows,
• 01:22 you'll declare the specific cells that you want to sum.
• 01:25 In PowerPivot, it's not that way.
• 01:27 We always say I'd like to sum the entire column, even if I only want five rows.
• 01:33 And then, we apply filters to remove the things that aren't applicable
• 01:36 to that particular scenario.
• 01:37 So it's looking at the problem from a 180 degree different direction.
• 01:41 And that's the part that can get a little confusing when actually trying to
• 01:44 figure out the measure calculation.
• 01:47 So what does that mean?
• 01:49 Well, the first thing that we need to work out is we need to figure out what
• 01:52 the filter context it before we can figure out how a measure is actually calculated.
• 01:56 So what makes up the filter context?
• 01:59 Well, the filter context is actually made up of a whole bunch of different things.
• 02:03 You can see a pivot table here.
• 02:04 So if we're looking at a measure like Avg Daily Sales, which is is average
• 02:09 of sales amount, and we want to try and debug this specific cell
• 02:14 with a 285.25, we need to first determine what is the filter context.
• 02:19 So the filter context for this particular measure
• 02:22 includes the year field because it's a filter field on the pivot table.
• 02:28 It includes the 10 to 15 because it's a row label.
• 02:32 So we've dragged the temperature range into the row labels field, and for this
• 02:36 specific cell of 285.25, the value in that filter context is the value of 10 to 15.
• 02:43 The month's short column is what you see at the top here, and
• 02:46 it's in this particular case, July.
• 02:48 So July is the actual filter context for this specific field of this calculation.
• 02:55 We can also see a slicer for account group, and it's filtered into Revenues.
• 02:58 So Revenues becomes part of that filter context.
• 03:01 So it's all of the components that drive and collide to come up with that 285.
• 03:06 So it’s the intersection of every roll label, every column label, every filter,
• 03:11 every slicer, and every timeline selections
• 03:14 that actually work out the regular filter context that we’re looking at.
• 03:20 What we can do, then,
• 03:21 is we can apply the underlying filter context to the source tables that we have.
• 03:26 So if you were to go into PowerPivot and set your filters to say, hey,
• 03:28 I want to filter year down to just 2009, because that's what's in the filter field.
• 03:33 I wanna filter the temperature range column down to just 10 to 15,
• 03:37 because that's what I see in the current context.
• 03:41 I wanna filter that month short field down to say just give me the July values.
• 03:45 And filter account group down to say just give me revenues.
• 03:48 Everything else is removed, and we end up with a much shorter table.
• 03:51 And at the very end of that process,
• 03:53 we perform the arithmetic on whatever's left and return it to our pivot table.
• 03:59 So this is the cycle that we go through every time we try and evaluate a measure.
• 04:05 And when we go to actually look at 358.15,
• 04:09 the next cell down, we start all over again.
• 04:12 So the filter context there, the year equals 2009,
• 04:15 the month short column is gonna equal July cuz its in the same column.
• 04:19 But the row its now gonna tell us that the temperature ranges 15 to 20,
• 04:23 because its actually moved down one more row.
• 04:25 The account group, of course, is still gonna be revenues.
• 04:28 If we get to the grand total, you've got a year of 2009,
• 04:31 your month short is July, your count group equals revenues.
• 04:35 But there's no filter context set for the average temperature
• 04:39 because you're using the grand total which contains all of the values.
• 04:43 So this is the trick, is being able to identify the filter context that you're
• 04:46 looking at to know which rows have been filtered out of the raw source table and
• 04:52 removed to allow the arithmetic to be performed on whatever's left.
• 04:57 And remember, every single cell you see in that pivot table starts again with that
• 05:02 raw unfiltered table, and is evaluated independently of every other cell.
• 05:07 That's the trick here,
• 05:09 that we're working with when were trying to understand how a measure calculates.

Lesson notes are only available for subscribers.