How to manipulate implicit measures in Power BI Desktop
Your email has been sent
Microsoft Power BI uses measures the same way Microsoft Excel uses functions. Learn how to recognize them in use and how to manipulate them when you want something different.
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.
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:
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.
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:
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
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.
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.
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
How to manipulate implicit measures in Power BI Desktop
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
The end of a year provides an ideal opportunity for revisiting the previous year’s goals and performance, reviewing personal and professional aspirations and prioritizing the upcoming year’s initiatives. But where to start? Tech pros can implement these seven planning steps from TechRepublic Premium to successfully set and track goals and subsequently prioritize initiatives. From the …
The role of a technical copywriter is recognized as a cornerstone in defining and conveying a company’s identity in the ever-evolving landscape of today’s business, where the online presence and narrative of a brand have outstanding value. This hiring kit from TechRepublic Premium provides an adjustable framework your business can use to find the right …
With artificial intelligence being more popular than free alcohol at a tech conference, it’s wise to stay informed about all things AI or even implement policies for its correct usage. This TechRepublic Premium pack provides readers with seven downloads for a bargain price. The bundle comprises two glossaries about AI and machine learning; three features …
A successful chief digital officer drives their organization’s digital transformation and creates value while rationalizing business processes and the customer experience. This hiring kit from TechRepublic Premium provides a workable framework you can use to find the best CDO for your organization. From the hiring kit: EDUCATION AND EXPERIENCE Candidates must have a degree in …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply