Locked lesson.

Upgrade

About this lesson

In this module we'll explore how to use Power BI's formula language to create re-usable "Measures"

Exercise files

Download this lesson’s related exercise files.

Basic Measures – Theory.docx
66.8 KB Basic Measures – Theory - Solution.docx
66.8 KB

Quick reference

Basic Measures – Theory

An overview of how and why you should create explicit measures.

When to use

When you want to begin writing basic DAX measures for Power Pivot.

Instructions

Advantages of explicitly writing your own formulas in Power Pivot:

  • You can provide discoverable names for your measures (instead of “Sum of Sales”)
  • You can reuse the measure on multiple PivotTables or PivotCharts
  • You can provide default formatting for your measures which will be applied every time you use the measure

The basic measure format:

  • Measure Name = FUNCTION(TableName[ColumnName])

Some helpful functions:

  • COUNT(Table[Column])
  • COUNTA(Table[Column])
  • DISTINCTCOUNT(Table[Column])
  • COUNTROWS(Table)
  • SUM(Table[Column])
  • MAX(Table[Column])
  • MIN(Table[Column])
  • AVERAGE(Table[Column])

Note that this is not an exhaustive list, there are many more

Hints & tips

  • Implicit measures are automatically generated when you add a field to the Values area of your Pivot Table
  • Explicit measures are measures you define yourself
  • The language we use for writing measures is Data Analysis eXpressions (DAX)
Login to download
  • 00:00 It's now time to start looking at
  • 00:04 implicit versus explicit measures.
  • 00:09 So what does this actually mean?
  • 00:12 Well, there's actually two types of measures you can create inside
  • 00:15 Power Pivot.
  • 00:16 Implicit is where you just take a field and you drag it into a pivot table and
  • 00:20 drop it in the values area.
  • 00:22 That will create what we call an implicit measure.
  • 00:24 So this is something like a sum of or
  • 00:27 account of, if you might think of a regular pivot table.
  • 00:31 Explicit measures are a where you actually define the measure by writing the formula
  • 00:35 yourself.
  • 00:37 This is, personally, my preferred way to do things here, and
  • 00:40 it's a best practice to actually write your own measures because
  • 00:44 you'll be able to know exactly what they do.
  • 00:47 Advantages of explicit measures, discoverable names,
  • 00:51 this is a beautiful thing.
  • 00:52 And as a matter of fact, when you define your own measure name, the pivot table
  • 00:56 won't change it to sum of or count of when you add it to the pivot table.
  • 01:00 It actually observes the name you chose, which is really kinda cool and
  • 01:03 very unlike the old standard calculated fields.
  • 01:08 You also get the ability to set default formatting so it's set once and for all.
  • 01:13 So every time you pull that individual measure onto a pivot table,
  • 01:17 the formatting will be correct.
  • 01:19 And man, if you've ever worked with calculated fields in a standard pivot
  • 01:22 table, you know how much of a bear that is, so this is a really cool thing.
  • 01:27 In addition, if you make an update to that specific field, it cascades
  • 01:32 through every single pivot table or every single dependent measure as well,
  • 01:37 both dependent measures and every pivot table in the entire, entire solution.
  • 01:42 This is really, really useful too, and
  • 01:43 it's something that you should not overlook.
  • 01:46 If you're dragging fields and dropping them on a pivot table,
  • 01:48 and realize that you made a mistake, hey, I put the units field instead of the sales
  • 01:52 field on five different pivot tables, well guess what?
  • 01:55 You now have to go fix all five of those pivot tables.
  • 01:58 If you've built one measure for sum of units, but you accidentally wrote
  • 02:03 it as sum of transactions, and you realize that is on five pivot tables,
  • 02:07 you fix the formula once and it's fixed everywhere, which is beautiful.
  • 02:11 To create a basic measure, there's a format that we actually generally follow.
  • 02:16 It looks like this, it's Measure Name equals, and
  • 02:19 then we have a FUNCTION name followed by the TableName, and
  • 02:24 then the ColumnName of the table that we're actually looking to aggregate.
  • 02:28 Now, you should always, always, always use fully qualified field names.
  • 02:32 And what that means is when you're referring to a column name that you wanna
  • 02:36 sum up,
  • 02:36 let's say that it's amount, you should always precede it with the table name.
  • 02:42 So if we're gonna be summing the amount column,
  • 02:44 we should be summing transactions amount.
  • 02:47 The reason why is actually pretty easy to see in this model because we have
  • 02:51 two tables that have an amount column.
  • 02:52 We've got transaction and we have budgets, so we want sum of transactions amount and
  • 02:58 sum of budgets amount to make sure that we're getting the right one.
  • 03:02 Now, there's all kinds of different formulas you can use inside Power Pivot.
  • 03:06 Here are some of the counting formulas that are available.
  • 03:08 You've got COUNT, you would preface ColumnName with the TableName, of course.
  • 03:14 We've got COUNTA, COUNTA counts textual values in addition to numeric,
  • 03:19 COUNT just counts numeric.
  • 03:21 We have DISTINCTCOUNT,
  • 03:22 this is something that does not exist in a regular pivot table.
  • 03:25 But if you use DISTINCTCOUNT,
  • 03:26 you can actually get a count of the unique items in a list.
  • 03:30 Pretty darn awesome, looking for that forever with a regular pivot table.
  • 03:34 COUNTROWS doesn't take a ColumnName as a parameter, but it will count the number of
  • 03:39 rows remaining in a table based on the actual filter context that's been set.
  • 03:45 There’s naturally some statistical formulas that you’re familiar
  • 03:47 with as well.
  • 03:48 Things like SUM, and MAX, and MIN,
  • 03:50 and AVERAGE, all of these exist inside Power Pivot’s formula language called DAX.
  • 03:56 Some examples of some simple measures, not based necessarily on the model that
  • 04:00 we’re building today, just some simples ones to give you some ideas.
  • 04:04 If you had a Sales Table, for
  • 04:05 example, Items Sold might be SUM of (Sale[Quantity]).
  • 04:08 And that would tell you quantity, total quantity of units sold.
  • 04:12 Unique Items would be DISTINCTCOUNT(Sales[Item]).
  • 04:15 So item would be item names here, so
  • 04:18 this would give you DISTINCTCOUNT to say how many unique items did I sell.
  • 04:22 I might've sold 500,000 items, but maybe I only sold 79 unique items.
  • 04:28 What's the Highest Price?
  • 04:28 It'd be the MAX of the (Sales[Price]) column.
  • 04:31 The Average Price would be the AVERAGE of the (Sales[Price]) column.
  • 04:34 And the Largest Invoice, of course, would be the MAX of the (Sales[Total]) column,
  • 04:38 so whatever the total invoice price was.
  • 04:41 If you were working with transactions and budget tables, like we are today,
  • 04:45 your Actual transactions or Actual revenue would be SUM of (Transactions[Amount]).
  • 04:50 Units Sold, SUM of (Transactions[Units]), if you had that particular field.
  • 04:54 And your Budget would be SUM of your (Budget[Amount]).
  • 04:57 We're gonna go now and step into building our own practical measures using our
  • 05:01 example dataset to see what these actually look like.

Lesson notes are only available for subscribers.

Gift this course