Power BI Intermediate Level: 37 - Quick Measures for Simple Custom Formulas

Table of Contents?|?Power BI Report File?| Sample Input

Short summary: You can easily create your own custom Power BI formulas using the Quick measure wizard, such as calculating the percentage difference of two sums. Power BI formulas are referred to as "measures". Measures are written using Power BI's Data Analysis Expressions (DAX) language.

So far on the beginner and intermediate level, we did summarizations in visuals using the drop down to the right of the column name which we want to summarize. For a text column, we can choose between Count and Count (Distinct), whereas for number column, we can additionally choose from Sum, Average, and etc. This is very simple and has brought us quite far already. In the example below with the data from the previous article for example, we could calculate the average menu item price that way.

Custom Measures (Formulas)

If the standard summarizations such as Sum and Average are not enough to get the desired result, you need to define your own custom Measures (formulas). Power BI uses its own language for that, called Data Analysis Expressions?(DAX). It is very powerful but takes some time to learn to apply properly. Please note that this is different from Power Query, which uses the M language. Power BI is easy to learn and hard to master, but rest assured I will explain you the important concepts for both DAX and M. With the right skills, you can do incredibly complex analysis in Power BI.

Quick Measure Wizard

An easy way to get started with measures is to use the Quick measure wizard. Let's assume we want to calculate the percentage difference between sum of menu item cost compared to menu item price, i.e. how much more we charge in percent for menu items compared to the cost for making them. As always, we are using made-up sample data.

On the right-hand side, select the table in which you want your Quick measure to land, then on the Table tools context ribbon, select Quick measure.

You can see roughly 30 calculations in different categories to choose from, such as Time intelligence and Mathematical operations. Many of the calculations you can customize further, giving you quite a lot of flexibility.

Let's scroll down and select Percentage difference in the Mathematical operations category.

Depending on the calculation you choose, you will be asked for different inputs and settings. In this case, choose the Menu Item Cost as Base value and the Menu Item Price as Value to compare. The default summarization is Count, so change the summarization to Sum in the dropdown. You can leave the setting for handling Blank values to the default.

Measure Naming and Home Table

You can see that Power BI added the measure to the table we selected, as indicated by the calculator icon next to the measure name in the table. If you click the measure name, the DAX formula bar opens with the measure definition. Rename the part before the “=” sign to shorten the name, let’s rename it to Menu Item Price vs Cost. A measure resides in a Home table. By default it is the table which you selected prior to Measure creation but you can change the Home table afterwards in Measure tools context ribbon under Home table. Which table a measure resides in is completely irrelevant. For that reason, it is common practice to create an empty “Measures” table and add all measures there. We will do that in the next article, for now let's skip that.

Measures in Visuals

To see the measure in action, let us create a table visual with Menu Item Name, Sum of Menu Item Cost, Sum of Menu Item Price, and our new Measure. Note that the Menu Item Name is used a regular column, whereas the other two summarized columns and our measure do calculations and thus also produce values in the Total row. Of course, you can use the measure in other types of visuals as well.

Note that the Total value for Menu Item Price vs Cost cannot be calculated from the values on the rows above. In Power BI, every single data point in a visual is independently calculated, including the totals. More on that in a later article.

Next up, let's look at the syntax and best practices to start writing our own DAX measures.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article:?DAX Measure Syntax and Best Practices