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

Microsoft Power BI does a lot of calculations internally, but if you need more, turn to Power BI Quick Measures – no DAX knowledge required.

Implicit metrics are internal calculations that Power BI generates automatically. Power BI also supports explicit metrics, which are more complex calculations in the form of quick metrics and model metrics. You create these yourself. They are easy to implement and change if necessary.

In this tutorial I will show you how to use Quick Measures for more complex calculation needs in a Power BI report.

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

How to use Quick Measures in Microsoft Power BI

Before you go to the trouble of writing a model metric that requires data analysis expressions, review the quick metrics user interface. Quick measures are calculations based on DAX, but you don’t need to know DAX to use them because Power BI writes the DAX formula for you based on your input values. It’s fast and powerful.

SEE: How to Create Reports in Microsoft Power BI (TechRepublic)

If you are unfamiliar with DAX, it is also a great way to learn this language. Even someone familiar with DAX will benefit from quick action by eliminating typing and syntax errors.

Using the demonstration file, let’s create a quick metric that returns the average sale for each region. If you haven’t already opened this file, do so by double-clicking on 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 into the bucket rows.
3. Expand the Sales table and drag Sale Amount into the Bucket Values ​​(Figure A).

Figure A

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

Power BI adds the quick measure to a table, so select the Sales table before doing 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 lightning bolt in the top left corner.

In the resulting window, select Average per category from the Calculation drop-down list. 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 drop-down menu 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 Dbut Power BI doesn’t show it with the table unless you add it.

Figure D

Before adding it to the matrix, you might want to change this long default name:

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

To add the new measure to the matrix visualization, activate it as shown in Figure E. This will add it to the matrix.

Figure E

The new metric works in other visualizations. For example, if you remove the Country-Region field and add City, the quick code updates accordingly, as shown in Figure F. You could shorten the name even further to “Average Sales” since it’s dynamic thanks to existing relationships, and “by Region” could be confusing for other designers who want to use it.

Figure F

How to decode the DAX in Power BI

The quick measurement uses the DAX language. To view it, click the measure in the Fields area. This displays the DAX formula in the formula bar, as shown in Figure G.

Figure G

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

stay tuned

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