Power bi calculated column vs measure

Let us try to understand here the calculated column in Power BI, what is a measure in Power BI, and Power bi calculated column vs measure.

• What is a Power bi calculated column?
• What is Measure in Power BI?
• How to see the value of the measure?
• Difference between calculated column and Measure in Power BI
• Choosing between Calculated column and Measure in Power BI

To understand the difference between calculated columns and measures in Power BI, first, we have to understand how are they working one by one.

What is a Calculated column in Power BI

• A Power BI calculated column is a column that calculated row by row in a data model. They take up computing space and increases the file size.
• A calculated column is a column like any other columns, created in the tables.
• Calculated columns are evaluated for each row in a table, immediately after we hit ‘enter’ to complete the formula.
• Power BI calculated columns are often used when we want to filter on the result rather than just a calculated result.

Example: Total as a Calculated column

Consider the below table we have created on products sold. Here we will calculate the total amount of each product by applying a simply calculated formula.

Click on the New column to create a calculated column on data table. Put this formula:

Total Amount = Product_table[Price]* Product_table[Quantity]

We can see the evaluation context for the calculated column is performed for each row or in other words, row by row calculations. And the calculation result of each row stored in the new column(Total Amount). This type of row by row calculation called Row context in DAX terminologies.

The result of the calculated column stores in the memory, like other columns. This means the more calculated memory you have, the more memory consumption, and as well as it will take more time to refresh the data.

Read: 19 Power bi best practices

What is measure in Power bi

• There is another way to defining calculations in the DAX model. Measures are used to calculated aggregate like Sum, Average, etc.
• Measures are created at the time of the query so they are not stored in the databases. It is safe to say that they do not use any memory spaces or RAM for storage purposes.
• A measure is a formula that is evaluated in the context in which it is used. Calculated measures are evaluated when we use them in a visual.
• In other words, a measure is calculated on the spot based user’s interactions with the report. The results of measures are not loaded during refreshes, rather it is calculated on the spot.
• Measures need to be defined in a table. To implement the DAX expression,it needs measure. We can easily move measure from one table to another table without affecting its functionalities because it does not belong to the table.

Example: Sum of Sales Amount

A very simple we can use as an example is a sum of Sales Amount. For calculating a measure, on the data table, click on New measures and put the below DAX expression:

Measure = SUM(Product_table[Total Amount])

We can see, a calculated measure is being created on the Fields pane. Aggregation can be done with a number of DAX functions as Sum, Average, SUMX, Substitute, etc.

How to see values of a Power BI measure

Measures are calculated in the fly. So we can see the value of the measure by putting that into a report. For this:

Go to the Report page. Select a card visual from visualizations. Then drag-drop the measure on the data field to visualize.

The above report shows the measured value of the Total sum. If we add a slicer, it will show the different values on the report.

Here we will add a slicer from the visualization, that slice the value as below:

The above report is sowing the measure calculation of the total sum of the selected Products (i.e. Mouse).

Read Power bi slicer filter another slicer

Power bi calculated column vs measure

Here are various differences between Power bi calculated columns and the measures:

1. Even if they both are looking the same but there is a big difference, the value of a calculated column is computed during the data refresh and uses the current row as its context in Power BI. But a Power BI Measure operates on aggregations of the data defined by the current context.
2. A calculated column does not depend on the user interaction in the report but in the case of Measure, it depends on the filter applied in the report such as slicers, rows, columns selection on a pivot table, filters applied to a chart.
3. It is important to understand that the Power bi calculated column uses RAM, which means the more column you have, the bigger your data model will be. But the Measures are evaluated at the query time and mainly use CPU.
4. A calculated column evaluates the results as row by row calculations or Row context, where a measure calculated based on all filters or Filter context.
5. Both Measure and calculated columns are using Power bi DAX expressions. But the difference is the context of the evaluations. A measure is evaluated in the context of the cell, that evaluated in the report or in a DAX query. But a calculated column is calculated at the row level within the table it belongs to.

Choosing between Calculated column and Measure

• We can use a calculated column, when the each row needs evaluating in Power BI.

Example: Calculate the total delivery charges.

Total Delivery charges = Product_table[Quantity] * Product_table[Delivery Charges]

The above example, it is showing that the calculated column is being displayed on a new column having data on each row.

• We can use the Measure for aggregation like SUM, SUMX, AVERAGE, etc.

Example: Average of Total amount

Average of Total Amount = AVERAGE(Product_table[Total Amount])

In the above example, the measure is calculated for aggregation. Also, it is not showing on the data table. But we can use it on reports from the fields data.

You may like the following Power BI articles:

• Power BI Column Chart
• Power bi row level security tutorial
• Power BI Funnel Chart
• Power BI waterfall chart
• Microsoft Power BI Stacked Column Chart
• Power Bi Schedule Refresh

Conclusion

From this Power BI Tutorial, we learned the concept of Power BI Measure and Calculated column. Also, we discussed:

• What is a Calculated column and Measure in Power BI?
• How to use the value of the measure?
• Difference between in calculated column and the Measure
• Choosing between Calculated column and Measure
• Power bi calculated column vs measure