How to manipulate implicit measures in Power BI Desktop

Image: dennizn/Adobe Stock

If you’re new to Microsoft Power BI Desktop and come from an Excel background, one of the first headaches you might run into is metrics. As you create visuals, you will benefit greatly, but you might also wonder how Power BI knows how to summarize your data without any intervention on your part. This is one of the great strengths of Power BI – it’s smart enough to evaluate data types and guess how you’ll want to summarize that data, then apply the appropriate metric to do so.

In this tutorial, we’ll define measurements and then observe them at work. We won’t create anything, but we’ll explore to clarify what Power BI automatically does for you, and then how to modify these default summaries if needed.

SEE: Windows, Linux, and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)

I am using Power BI on a Windows 10 64 bit system. You can download the .pbix demo file, AdventureWork Sales from Github. Once downloaded, double-click the .pbix file to open it in Power BI and follow. 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 are similar to Excel functions, VBA user-defined functions and LAMBDA functions in Excel because they perform analysis such as sum, average, minimum, maximum, count, etc. for you. Similar to Excel functions when you change data, metrics update when you interact with reports. Like LAMBDA functions, you can assign meaningful names to explicit measurements that are easy to remember and support arguments.

There are two types of measures in Power BI:

  • Default metrics are built-in and ready to use.
  • Model metrics are metrics that you create — they are self-explanatory. This includes quick metrics, which provide an interface you’ll use to create the metric. You will also use the Data Analysis Expressions (DAX) library to write model metrics.

Use default metrics if possible, but you’ll often need to create your own. When you do, Power BI adds quick and template 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 when you interact with Power BI reports the same way Excel functions do when you filter, add, change, and remove values. Quick metrics are similar to functions, and pattern metrics are comparable to VBA user-defined LAMBDA functions, expressions, and functions.

How to find implicit measures in Power BI

Most of the calculations are done without any effort on your part. These are 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 indicates the following:

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

Note that there is 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. This means the field is related to 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

The Sales table offers several implicit measures.

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

In the Fields pane, expand the Sales table if necessary. Next, 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 can change the measurement, but don’t now. Feel free to go through all the fields in this table using the Columns Tool tab, but don’t make any changes. You’ll use this tab often, especially when working with someone else’s data.

Figure B

Use the Column Tools tab to learn how Power BI summarizes a field.

How to edit implicit measures in Power BI

Let’s take a quick look at how implicit metrics work by adding Sales Amount to the current visual. To do this, click inside the visual, then check the sales amount, as shown in Figure C. The default sum metric displays a sum of annual sales — and you’ve done nothing but add the field to the visual.

Figure C

Add a field to a visual to see how Power BI summarizes the data.

Here’s a quick tip when adding visualizations: instead of dragging a field to a bucket, check it in the Fields pane as shown 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 metrics.

Now let’s try 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 Summary drop-down list. Next, check Sales Amount in the Fields pane to add it to the visual. As you can see in Figure DPower BI now calculates average sales.

Figure D

Change the summary to return different information about sales values.

Let’s dig a little deeper to find out what the average unit price per month was. To do this, simply check Unit Price in the Fields pane. The results, presented in Figure E, show the average for each month and year. This field automatically defaults to the average, because Power BI recognizes that the sum of this field would not make sense.

Figure E

Add the average unit price.

Power BI will let you 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 summary metric, which is a little faster than deleting it and adding it back with the different metric.

Stay tuned

If you’re new to Power BI, metrics are a simple concept. Because Power BI applies implicit measures to so many fields for you, Power BI frees you from doing it 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.


Comments are closed.