Basic Measures – Theory

Quick reference

• 00:05 If you think back to the Power BI development process,
• 00:08 step one is all about getting and transforming data.
• 00:11 Step two is about relating those tables together so
• 00:14 that we can actually build more complex models.
• 00:16 We're now at step three, which is about the development and
• 00:19 implementation of measures.
• 00:22 Now, so far, in the projects that we've built,
• 00:24 we've been using what we call implicit measures.
• 00:27 An Implicit Measure where you drag a field onto the visual.
• 00:30 Now we might click a little drop-down arrow and
• 00:32 change the type of it, but really it's pretty much done for us.
• 00:36 Very similar to an Excel pivot table that when we drag a field on,
• 00:39 it automatically produces a sum or of a count.
• 00:41 This is the same thing that happens in Power BI Desktop, and that's called,
• 00:45 as I say, an implicit measure.
• 00:47 There's also a whole another class of measures which are called
• 00:50 explicit measures.
• 00:52 These are measures where you define it,
• 00:54 you write the formula that actually returns the results manually.
• 00:59 Now, I am a big fan of explicit measures.
• 01:02 They have some distinct advantages over using implicit measures in my opinion.
• 01:07 Number one is discoverable names.
• 01:09 You can actually name your measure and you can drag it onto on your visuals, and you
• 01:12 know that wherever you see that name, it's going to be the exact formula you wrote.
• 01:16 That's kind of important.
• 01:18 Number two is that you can set default formatting for your measures.
• 01:22 This is really cool because you know that if you set it to two decimal places,
• 01:25 when you drag it onto a new visual, it's gonna have two decimal places.
• 01:28 That's pretty awesome.
• 01:30 Another major advantage of an explicit measure over an implicit measure is
• 01:35 if you drag the wrong field onto several visuals by accident, and
• 01:39 then you need to update them, you would have to update every single visual.
• 01:44 If you define your explicit measure and you drag it on to three different visuals,
• 01:48 and then you realize the formula's not quite right,
• 01:51 you can update the formula once and it fixes all visuals at once.
• 01:54 So it's much more robust, it's actually a portable formula that works really well.
• 01:58 The other thing, and we'll just touch on this really briefly when we get into
• 02:01 the demo in the next module,
• 02:02 is that it has a security feature that disables this feature called See Records.
• 02:06 And I think that's actually kind of a useful thing to have.
• 02:08 That happens by fault with an explicit letter as well.
• 02:13 So how do you create a basic measure pattern?
• 02:17 Well, we essentially have to write a formula to
• 02:20 aggregate the columns in the Power BI Desktop model.
• 02:23 The way that we do this is we write our measures like this, we provide the measure
• 02:27 name and then we see equals, what is the aggregation function we're gonna use,
• 02:31 open the round brackets, and then you type in your TableName[ColumnName].
• 02:35 And the ColumnName isn't always necessary.
• 02:37 In most cases, it generally is.
• 02:39 This is what we call using fully qualified names.
• 02:42 The reason it's fully qualified is because we're prefacing the ColumnName with
• 02:46 the TableName.
• 02:47 That's a best practice that you never want to avoid when you're building a measure.
• 02:51 This actually makes the formulas much more portable and that way, we always know or
• 02:55 the system always knows which columns it's actually aggregating,
• 02:59 especially if the column name appears on multiple different tables.
• 03:02 It knows which one to pick up then.
• 03:05 The easiest way to do this is you right-click on the table name
• 03:09 in the fields list in Power BI Desktop and click Create Measure.
• 03:12 In this way, they automatically even get sorted to the right place.
• 03:14 I'll show you in our next module.
• 03:16 Now, there's a few different formulas here that we can use for
• 03:19 doing things like counting and what not.
• 03:21 Counting formulas include something like COUNT,
• 03:24 very similar to the old Excel function, it counts numbers.
• 03:27 COUNTA which will count text.
• 03:30 In Power BI, we also have a DISTINCTCOUNT function.
• 03:33 This is super useful if you're trying to figure out how many distinct sales items
• 03:36 did I sell.
• 03:38 This other little function called COUNTROWS actually counts the visible
• 03:41 number of rows in a table.
• 03:43 So we don't need to put in the column name for this.
• 03:44 We just provide the table name and it'll give us a count of all of the records that
• 03:48 have not been filtered out of the solution, which is kind of nice.
• 03:50 As you might expect, there's also statistical formulas.
• 03:53 Things like SUM, and MAX, and MIN, and AVERAGE,
• 03:56 all of these things are formulas you would expect to be there.
• 04:00 And there's obviously a bunch more as well,
• 04:02 but these are some of the highlight ones that you're generally gonna use in many,
• 04:06 if not most, of the projects that you build.
• 04:10 So, what about some examples of some simple measures?
• 04:13 Well, if you had some items that were based on, say,
• 04:16 a sales table, you might make a measure for items sold that would look like this.
• 04:21 Items Sold = SUM(Sales[Quantity]), sales being the table, and
• 04:24 quantity being the column that holds the quantities.
• 04:27 It sums up all the quantities in that column, and there's your items sold.
• 04:31 Unique items, it would create by saying =DISTINCTCOUNT(Sales[Item]).
• 04:35 So this would be a potentially text in the item column, but
• 04:38 we're getting a distinct count of those particular items, which is kinda cool.
• 04:41 So even though you may have sold 40,000 items, you may have only sold 15
• 04:45 unique items, because you only have 15 or 20 sales products.
• 04:49 So that's a way to get that information back.
• 04:52 What's the highest price?
• 04:54 Let's pick up the max of the sales price call or the average price.
• 04:58 How about the average of the sales price?
• 05:00 The largest invoice could be the max of the total invoice column
• 05:03 on your sales table.
• 05:04 So this is relatively simple straightforward formulas.
• 05:07 They're very similar to Excel and that's actually by design.
• 05:11 The Power BI team that worked on this particular product in the back end which
• 05:14 is also built on Excel's Power Pivot used these formulas to try and
• 05:18 take Excel knowledge and use it.
• 05:22 Other things you might want if you're basing your information on transaction and
• 05:25 budget tables, we could come up with an actual measure,
• 05:27 sum of transactions amount, or a unit sold, sum of transactions unit.
• 05:32 How about a budget, sum of budget amount?
• 05:34 So all of these are single column, very easy measures to make,
• 05:39 to start driving other business intelligence forward.

Lesson notes are only available for subscribers.