How to add quick measures for complex calculations in Microsoft Power BI Desktop

Microsoft Power BI does a lot of calculating internally, but when you need more, turn to Power BI quick measures—you need no knowledge of DAX.

Implicit measures are internal calculations that Power BI generates automatically. Power BI also supports explicit measures, which are more complex calculations in the form of quick measures and model measures. You’ll create these yourself. They’re easy to implement and change as needed.

In this tutorial, I’ll show you how to use quick measures for more complex calculating requirements in a Power BI report.

I’m using Power BI Desktop on a Windows 10 64-bit system. You can download the demonstration .pbix file, AdventureWorks Sales from GitHub. Once downloaded, double-click the .pbix file to open it in Power BI and follow along, or use a .pbix file of your own.

How to use quick measures in Microsoft Power BI

Before going to the trouble of writing a model measure, which requires Data Analysis Expressions, check the quick measures user interface. Quick measures are calculations based on DAX, but you don’t have to know DAX to use them because Power BI writes the DAX formula for you based on your input values. It’s quick and powerful.

SEE: How to build reports in Microsoft Power BI (TechRepublic)

If you’re not familiar with DAX, it’s also a great way to learn this language. Even someone well-versed in DAX will benefit from quick measures by eliminating typos and syntax errors.

Using the demonstration file, let’s create a quick measure that returns the average sale for each region. If you haven’t opened this file yet, do so by double-clicking it in File Explorer. In the Reports window, do the following:

1. In the Visualizations pane, click Matrix. A matrix keeps the example simple.
2. Expand the Customer table, and drag Country-Regions to the Rows bucket.
3. Expand the Sales table, and drag Sale Amount to the Values bucket (Figure A).

Figure A

Thanks to an internal implicit measure and relationships, Power BI automatically totals values in the Sale Amount field for each region. Let’s add a quick measure that averages the sales total for each region.

Power BI will add the quick measure to a table, so select the Sales table before you do anything else. You can put it in another table, but the Sales table makes the most sense. With the matrix visualization selected, click the quick measure icon; it’s the one with a bolt of lightning in the top-left corner.

In the resulting window, choose Average Per Category from the Calculation dropdown. Category is a static term, not the name of a field. Expand the Sales table and drag Sales Amount to the Base Value bucket. Click the dropdown and choose Average (Figure B).

Figure B

Expand the Customer table, and drag Country-Region to the Category bucket (Figure C). Click OK.

Figure C

Power BI adds the new quick measure to the Sales table, as shown in Figure D, but Power BI won’t display it with the table unless you add it.

Figure D

Before you add it to the matrix, you might want to change that long default name though:

1. Right-click the measure and choose Rename.
2. Enter the new name Average Sale by Region. You can add the word “measure” to the name if you like, but the icon to the left identifies the item as a measure.

To add the new measure to the matrix visualization, check it as shown in Figure E. Doing so adds it to the matrix.

Figure E

The new measure will work in other visualizations. For instance, if you remove the Country-Region field and add City, the quick measure updates accordingly, as shown in Figure F. You could shorten the name even more to “Average Sale” because it’s dynamic thanks to the existing relationships, and “by Region” might be confusing to other designers who might want to use it.

Figure F

How to decipher the DAX in Power BI

The quick measure uses the DAX language. To see it, click the measure in the Fields pane. Doing so displays the DAX formula in the Formula bar, as shown in Figure G.

Figure G

This formula is simple, but you might have questions. The KEEPFILTERS function makes sure Power BI sorts the regions. The CALCULATE function calculates an expression in the current context, which in this case is the AVERAGE function.

Stay tuned

Now that you know how to create a quick function, you’ll probably want to learn more about DAX. In a future article, I’ll show you how to use DAX to write model measures for those times when you need a complex calculation.