How to manipulate implicit measures in Power BI Desktop

If you’re new to Microsoft Power BI Desktop and coming from an Excel background, one of the first puzzles you might encounter is measures. As you build visuals, you’ll benefit from them greatly, but you might also wonder how Power BI knows how to summarize your data without any input from you. This is one of Power BI’s great strengths — it is smart enough to evaluate data types and guess at how you’ll want to summarize that data and then apply the appropriate measure to do so.

In this tutorial, we’ll define measures and then watch them at work. We won’t create anything, but we will explore in order to clarify what Power BI does for you automatically and then how to change those default summaries when needed.

SEE: Windows, Linux and Mac commands everyone needs to know (free PDF) (TechRepublic)

I’m using Power BI on a Windows 10 64-bit system. You can download the demonstration .pbix file, AdventureWork Sales from Github. Once downloaded, double-click the .pbix file to open it in Power BI and follow along. Power BI online supports implicit measures. You’ll find them in the Build pane. However, the instructions are for Power BI Desktop.

What is a measure in Power BI?

Power BI measures are calculations. They’re similar to Excel functions, VBA user-defined functions and LAMBDA functions in Excel because they perform analyses, such as sum, average, minimum, maximum, count and so on for you. Similar to Excel functions when you change data, measures update as you interact with reports. Similar to LAMBDA functions, you can give explicit measures meaningful names that are easy to remember, and support arguments.

There are two types of measures in Power BI:

• Implicit measures are built-in and ready to go.
• Model measures are measures that you create — they are explicit. This includes quick measures, which supply an interface that you’ll use to create the measure. You’ll also use the Data Analysis Expressions (DAX) library to write model measures.

Use implicit measures, when possible, but you’ll often need to create your own. When you do, Power BI adds quick and model measures to the Fields list for quick access. Model measures are based on DAX, a library of functions and operators that you’ll combine to build expressions in Power BI, Analysis Services and Power Pivot in Excel.

Think of measures as calculations that update as you interact with Power BI reports in the same way Excel functions do when you filter, add, change and delete values. Quick measures are similar to functions, and model measures compare to VBA user-defined functions, expressions and LAMBDA functions.

How to find implicit measures in Power BI

Most calculating goes on without any effort on your part. That’s quick measures at work. The Fields pane displays all the tables in the file. As you can see in Figure A, the Sales table has several fields. The sigma symbol denotes the following:

• The column is numeric.
• The column will summarize values when added to a bucket, also known as a field well.

Notice that there’s no measure for the Unit Price Discount field. Sometimes Power BI is smart enough to interpret a field’s purpose as a non-additive field. That means that the field is related to the other fields in some way but like an address or phone number, Power BI won’t apply an implicit measure to the field.

Figure A

You might be wondering what the symbol next to the Profit Amount field means. This symbol denotes a calculated column — a new column you create using DAX. Calculated columns are not the same as model, or explicit, measures. This is part of the demonstration .pbix file.

In the Fields pane, expand the Sales table if necessary. Then, select the Sales Amount field (don’t check it). To learn more about this field, click the Columns Tool tab.

As you can see in Figure B, the implicit measure will sum this field. You could change the measure, but don’t do so right now. Feel free to review all of the fields in this table using the Columns Tool tab, but don’t make any changes. You will use this tab often, especially when working with someone else’s data.

Figure B

How to change implicit measures in Power BI

Let’s take a quick look at how implicit measures work by adding Sales Amount to the current visual. To do so, click inside the visual and then check Sales Amount, as shown in Figure C. The implicit sum measure displays a yearly sales sum — and you didn’t do anything but add the field to the visual.

Figure C

Here’s a quick tip when adding visualizations — instead of dragging a field to a bucket, check it in the Fields pane as instructed above. Power BI does a great job of evaluating whether the field represents a legend, an axis or a value to calculate. We’ll explore this behavior to see how Power BI applies implicit measures.

Now let’s try that again. Uncheck the Sales Amount field to remove it from the visual. Then, select it (don’t check it). Click the Columns Tool tab. In the Properties group, choose Average from the Summarization dropdown. Then, check Sales Amount in the Fields pane to add it to the visual. As you can see in Figure D, Power BI now averages the sales.

Figure D

Let’s go one step further to learn what the average unit price was per month. To do so, simply check Unit Price in the Fields pane. The results, shown in Figure E, show the average for each month and year. This field automatically defaults to average because Power BI recognizes that summing this field would make no sense.

Figure E

Power BI will allow you to change the measure, but it won’t default to the sum measure. Once a field is in the visualization, you can select it and change the Summarization measure, which is a bit quicker than removing it and adding it back in with the different measure.

Stay tuned

If you’re just beginning with Power BI, measures are a simple concept. Because Power BI applies implicit measures to so many fields for you, Power BI relieves you of doing so yourself, or having to create an explicit measure.

Implicit measures aren’t meant for complex expressions, so in a future article, I’ll show you how to create explicit measures.