Sign in or start a free trial to avail of this feature.
2. Creating and Using Measures
Measures are calculations that can be added to the data model. In this lesson, we’ll create some new measures and learn when to create a measure and when to create a column.
Lesson Goal (00:20)
The goal of this lesson is to generate a more accurate unit price calculation in our data set by using a measure.
Comparing Columns and Measures (00:26)
A measure is a single calculation performed on aggregated data in the Power BI data model. This contrasts with a calculated column, which performs a calculation for each row in the relevant table. The formula is only calculated when we add it to a visualization. In our case, we use a measure to calculate the average unit price for the whole data set, without worrying about the number of transactions in each row.
Creating a Measure (00:55)
A measure can be created by selecting New Measure from the Home tab or the Transform tab. This displays the formula bar at the top of the view. We enter the measure name, and the formula that will create the measure. When creating a measure, the formula must include aggregations for each columns. For example, we use the SUM function to calculate the total sales and total quantity for our data set, then divide these figures to produce the overall average unit price for the data set.
After we create a measure, it appears in the fields list and can be added to visualizations. In our case, there is a considerable difference between the unit prices computed using a column and using a measure. This illustrates the importance of understanding how DAX formulas are calculated.
When to Use Columns and Measures (02:32)
A calculated column is only calculated when it is first created. As a result, it should be used to calculate a field that will have a fixed value for each row. Columns are also useful for creating a field that will be used in filters or slicers. Measures are used to perform calculations with aggregate data. They’re also useful if you want to create a calculation that will adjust automatically when you apply filters or slicers.
In general, it’s best to use measures unless you have a good reason to use columns. Columns make your data model larger, which can eventually impact performance. As a result, it’s good to limit their use if possible. Finally, you should always ensure you understand the calculation contained in a column or measure. If necessary, replicate the calculations outside Power BI and see if you can get the same results.
In the previous lesson, we created a calculated column to determine the average unit price of each of our products. We learned, however, that using a calculated column could not give us the solution we're looking for, as our calculated columns did not account for the varying size of transactions. In this less, we'll generate a more accurate unit price using a measure.
While a calculated column creates multiple numbers, one for each row of our data set, a measure creates only a single number. For each product, we want to determine the average unit price by dividing the total price of all sales by the total quantity sold.
We're not interested in how many units were sold in each transaction, so using a measure makes sense here. In effect, a column is a new field that is calculated and added to the data model, while a measure is a formula that's only calculated when it's used in a visual.
To create a measure, we'll navigate to the Modeling tab and select "Create Measure." Remember that we used the data view when creating a column so that we could see the column being added to the data set. However, a measure is not added in the same way, so the report view is just as convenient.
As with a calculated column, creating a measure prompts the formula bar to appear at the top of the view.
We'll call the measure "unit price measure" and enter a formula for the sum of sales divided by the sum of quantity.
This formula will use the sum function to obtain the total sales and total quantity for the entire data set.
We'll press "Enter" and see if the measure appears in our Fields list with a calculator symbol, indicating it's a measure.
We can now add the existing measure to our existing visualization.
We'll simply drag the unit price measure on top of our existing bar chart, creating a second bar for each product.
The green bars show the average unit price based on our calculated column, while the black bars are based on the measure we just created.
As you can see, there's a considerable difference between the two bars for some products.
Remember, it's the measure and the black bars that produce the correct average unit price for each product.
The measure is only evaluated when it's added to this chart, so it only performs one aggregated unit price calculation for each product.
This demonstrates the importance of understanding what you're actually computing when you create DAX formulas.
DAX and Power BI are capable of quickly performing complex calculations and visualizing them in charts. However, if you don't understand how those calculations work, then your charts may do your business more harm than good. If you're unsure whether to create a calculated column or a measure, consider the desired output. You should use a calculated column to determine a fixed property for each row, or if you want to use a column as an attribute in filters or slicers.
For example, a categorical field should generally be created as a calculated column.
You should use a measure when you want to perform a calculation with aggregate data. Measures are also best when you want the results of the calculation to adjust based on filters and slicers.
Since measures are calculated every time they're used in a visual, they're dynamic and responsive to changes.
A good rule of thumb is to use a measure by default, unless you have a good reason not to. This is because calculated columns make your data model larger.
Individuals with Excel backgrounds often tend towards calculated columns, as they feel more familiar, but in practice, it's best to stick with measures unless you clearly need to use columns.
As a general piece of advice, you should make sure you can understand what a calculated column or measure is actually computing.
If you have to, replicate the calculations yourself outside Power BI and see if you can get the same results. If you can, you're well on your way to understanding whether a column or a measure is appropriate in your situation.
We'll stop the lesson here. In the next lesson, we'll look at a third type of calculation, calculated tables.