Our instructors in Power BI training recommend creating calculated measures over calculated columns because calculated measures are memory-friendly alternatives for calculated columns. Calculated measures are an easy way to define calculations in a DAX model. However, it is important to understand the evaluation context around measures before creating them.
Here’s a quick guide to understand evaluation context in DAX: https://powerbitraining.com.au/what-is-evaluation-context-in-dax/
What is a Calculated Measure:
Calculated measures are used to perform aggregations. They do not add a value for every row in the table. This makes measures memory efficient as they do not appear in your data set. They are lightweight alternatives to calculated columns. You cannot however use naked columns while writing regular DAX expressions for creating a measure.
Measure is used in the values area of a visualisation. Therefore, they are only created when a measure is used in a visual. Following the best practise, we will be creating a separate table for measures.
To create a table for measures:
- Click on Home tab in the Ribbon.
- Click on Enter data in the Data
A pop-up menu appears.
- Name the new table to All Measures.
- Click load.
Now that a table for measures is created. Let’s start creating measures.
Before we create our first measure, lets quickly get affiliated with the simplest function in DAX.
To create measure:
- Right click on All Measures table in the Fields
- Click on New measure.
- Type the follow DAX code in the formula bar in the measure tools and press Enter.
In this Expression:
- The aggregated value of Total Product Cost for the entire column is subtracted from the aggregated value of Sales amount for the entire column.
- Click on the Profit (CM) in the All Measures
Measure tools menu opens,
- Click on the “$” and type 2 in the box.
- Create another measure by following the steps 5-6.
- Type the following DAX code in the formula bar in the measure tools and press Enter.
In this Expression:
- The profit margin % is found by dividing the profit calculated in the previous measure by the total sales amount.
- Click on Profit Margin % (CM) in All Measures.
Measure tools open
- Click on the % sign in the formatting section.
- Click on the matrix visual in the visualization pane.
- Drag and drop Category and Subcategory from the Products table to the rows.
- Drag and drop Sales Amount from Sales table to the Values.
- Drag and drop Profit (CM) and Profit Margin % (CM) from All Measures to the Values.
Notice the change in percentage values.
Conclusion:
Calculated measures are memory-friendly alternatives of calculated columns. They are particularly suitable for ratio and percentage calculations which involve aggregation in the calculation. This blog introduces the concept of a calculated measure using some business examples. A step-by-step guide to create calculated measures using the Adventure Works data set has been presented.
Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide our services in the following regions
