Our Power BI DAX training upskills the attendees with the DAX concepts needed to realize the full potential of Power BI. Understanding each of the DAX concept correctly is important to write accurate calculations. In this blog post, a use case of DAX concepts is discussed and a comparison between the calculated column and calculated measure is provided.

Before understanding the difference between the two, we recommend you develop a good understanding of each of them individually by following the blogs listed below:

• Calculated Column in DAX: https://powerbitraining.com.au/calculated-columns-in-dax/
• Calculated Measure in DAX: https://powerbitraining.com.au/calculated-measure-in-dax/

When do Calculated Columns fail?

Let’s create a few more calculated columns to understand when do calculated columns fail.

To create a calculated column:

1. Right click on the Sales table in the Fields
2. Click on New Column.

Column tools open.

3. Type the following formula in the formula bar and press Enter

In this Expression:

• The Profit Margin % is found by dividing the profit by Sales Amount for each transaction.

4. Click on % in the formatting section.
5. Click on the matrix visual in the visualization pane.
6. Drag and drop Category and Subcategory from the Products table to the rows.
7. Drag and drop Sales Amount, Profit (CC) and Profit Margin % (CC) from Sales table to the Values.

Take a minute to analyze the numbers in the Profit Margin (CC) column.

8. Right click on the Profit Margin % (CC) in the values field and check the aggregation.

Notice that none of the aggregation available suits the requirement of percentage calculation because  .

When to use Calculated Column or Calculated Measure?

Now that we have a situation where calculated columns fail, let’s consider a situation when calculated columns are the best solution.

To create a calculated column:

1. Right click on the Sales
2. Click on New Column.

Column tools open.

3. Type the following code in the formula bar.

In this Expression:

• In the first part of the IF() function, a logical test for the Unit Price Discount Pct is performed.
• If the test returns TRUE this means that the discount is 0 so the cell in the new column is assigned a string value of “No Discount”.
• When the IF() returns FALSE, then another logical test on the Profit column is performed and Discount is categorized based on the profit.

4. Click on the matrix visual in the visualization pane.
5. Drag and drop Category and Subcategory from the Products table to the rows.
6. Drag and drop Sales Amount from Sales table to the Values.
7. Drag and drop Profit (CM) and Profit Margin % (CM) from All Measures to the Values.
8. Click on the slicer in the visualization pane.
9. Drag and drop Discount Category (CC) from the Sales table to the Field area.
10. Click on Discount with Profit.

Notice that the slicer values have been used to slice the data.

Now that you’re familiar with the calculated columns and calculated measures, let’s look at situations when they should be preferred over the other.

Calculated Column vs. Calculated Measures

Both of them have their own use cases and the choice depends on the business requirement. There are few points to consider when you’re choosing between a calculated column and calculated measure.

Conclusion:

Creating calculated columns and creating calculated measures are both important concepts in DAX. Although, they both enrich the data set by adding more information on the top of raw data, but it is important to understand the use case for each of these so that the calculations performed are accurate. This blog post presents a use case and enlists the difference between the two concepts.

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

Power BI Training in Sydney

Power BI Training in Melbourne

Power BI Training in Brisbane

Power BI Training in Canberra