Power BI is a powerful data visualization application that enables analysts to perform data analysis using bespoke computations. Calculated Columns and Measures are the most common types of calculations. Both are written in DAX (Data Analysis Expressions), Power BI Desktop’s primary formula, query language and make most of the part of Power BI calculations. Let’s check out how calculated column & measure works in Power BI.

 

What is a Calculated Column in Power BI?

A calculated column is a table extension that is assessed for each row in the table. Calculated columns, just like all the other data imported from a data source, are stored in Power BI’s xVelocity in-memory storage. With one exception, a calculated column is nearly identical to a non-calculated column. DAX formulas and values from other columns are used to determine their values.

Calculated columns are only evaluated when you first define them and during a dataset refresh because they exist at the same level as your tables. Deriving the per-unit profit of a product by deducting the cost of the product from the product’s price is an instance of a calculated column.
 

What are the highlights of the Calculated Column?

• Calculation by row: Row Context
• Retained in memory (consumes RAM)
• Computed at the time the report is refreshed (either scheduled basis or manual)

 

Understanding Calculated Columns: A Wider Aspect

Calculated columns can be extracted from the original data source or produced using a DAX expression in Power BI. The best way to think of columns is as static row-level properties.

Example: Profit as a calculated column

Think of a table, which contains sales and expense data. In such a table, profit would be calculated by subtracting costs from sales for each row. As a result, this is essentially a calculated column.

Expression:  Profit = Fact Internet Sales [Sales Amount] – Fact Internet Sales [Total Product Cost]
 

Row by Row Calculation: Row Context

One of the most crucial things to remember about the calculations you perform in the Calculated Column (in the majority of cases, but not always) is that they are performed one row at a time or row by row.
 

Stored in Memory

Calculated Column, like any other column, keeps values in memory. The calculation takes place during Refresh time, and the result is saved in memory.

This means that the more computed memory you have, the more memory you will consume and the longer your refresh time will be. Nevertheless, because many calculations are simple, your refresh time may not be significantly impacted.
 

Calculated Columns: Rules to Consider

When you need to filter your data model using the outcomes. In the Pivot Table, you can’t utilize a Measure on Rows, Columns, Filters or Slicers. If you wish to do this, you’ll need a Column rather than a Measure. Rather than using a Calculated Column, it is better to have the column introduced to your data source.

• You can reuse the source column in other workbooks without having to rewrite the calculated column if you load it from the source.
• Import compression has the potential to be improved.
• Your worksheet will refresh more quickly.

However, if importing from the source isn’t possible, a Calculated Column is your best buddy.

1. When you’re improving a Lookup (Dimension) table: Since lookup tables are typically smaller (with fewer rows), the negative effects of Calculated Columns aren’t as severe.
2. When the cardinality of the Calculated Column findings is low: In Power Pivot, the uniqueness of values in a column is the enemy of compression. So, if your Calculated Column returns two possible unique values (e.g., Yes or No) and this column assists you in designing a sophisticated Measure, the Calculated Column is fine. Low cardinality calculated columns compress extremely well.
3. When you have a complex formula: A complex formula that is stressful on your report to the point where the inadequate run time performance of a measure calculation makes it advisable to “pre-calculate” the result and save it in a column. Although it is doubtful that a self-taught Excel user will write such a sophisticated formula in the early stages of learning Power Pivot, this is an acceptable use case, nonetheless.

With the aforementioned criteria & use cases in mind, you should be able to figure out when to utilize calculated columns & when not to.
 

Problems of Calculated Columns

1. For each and every row in the table, the results of your formula are calculated and recorded. This consumes disc space as well as RAM, making your report less performant. It probably doesn’t matter if your table has 100 rows. It can be a major issue if your table has 100 million rows.
2. Calculated column compression may not be as excellent as imported column compression. As a result, Power Pivot does not prioritize Calculated Columns in the same way that it does imported columns.
3. When the worksheet is updated against the data source, the Calculated Columns are recalculated. This will make the refresh process take longer.

 

What is Measure?

A measure is typically a calculation performed at the aggregated level. This aggregation can be as basic as adding up sales or as complicated as computing monthly average sales over a rolling 12-month period. Measures are dynamic, affecting a subset of data from one or more tables. As a result, the subset of data can be altered using the Power BI Report’s filters, and the calculation will have to be reviewed dynamically. As a result, Measures are not pre-calculated; instead, they will be calculated as needed when they are included in the report.

According to radcad.com – “In fact, 70% of your time when you write DAX is used for writing measures, if not more!”

 

What are the highlights of Measures?

• Calculated using all of the filters: Contextual Filtering.
• Isn’t saved and isn’t calculated ahead of time.
• When you put it on a report page and alter a slicer, filter, or tap on a column chart or any other visual to highlight and it affects this measure’s value, it’s computed on the go.
• Calculation uses the CPU.

 

Understanding Measure in Power BI: A Wider Aspect

In brackets, measures are referred to by their names (e.g. [Measure Name]). The best way to think of measures is as dynamic aggregations. The level at which they are computed will ultimately determine their worth. They are table-agnostic and give developers a wide range of analytical options.
 

Sum of Sales

The majority of the times, measures are aggregations. A sum of sales is a relatively simple aggregate that we may use as an example.
Aggregating can be accomplished using a variety of DAX functions, including Sum, SumX, Average, Calculate and a plethora of other aggregation functions. Let us now address the most crucial question:
 

How to see the Value of the Measure?

Measurements are made on the fly. In reality, this is one of the most fundamental distinctions between a measure and a computed column. So, if measure values are calculated on the fly, how can you know what they are? The answer is to include it in a report.
 

Measures: The RAM and CPU Outlook

The calculation of the measure is done on the go. This signifies that the value of the measurement is not saved in the memory. Memory or RAM will not be used in any way by the measure. Measures, on the other hand, use the CPU because their calculations must be completed at the time of visualization. If you modify a filter or slicer, you’ll need to recalculate. Because the reaction time must be quick, this calculation is performed by the CPU.
 

Power BI Measures vs Calculated Columns: Which one is better?

When To Use a Power BI Measure Vs A Column in Power Bi?

The primary distinction: The key distinction amongst calculated columns and measures is that columns are evaluated at each row, whereas measures are only examined at the level of granularity in which they are shown. Columns are recalculated at each visual contact, whereas measures are recalculated at each data refresh. This means that, unlike measures, column values are not responsive to filter selection or parameters.
 

Power BI Calculated Column vs Measure Advantages

Here are some of the advantages of utilizing Measures rather than Calculated Columns.

1. Your workbooks will be smaller and quicker to complete. You are not “materializing” and saving the results of every conceivable calculation in your table in memory and on disc (like with a Calculated Column). Measures are instead calculated on the fly in memory on order, and Measure calculations are typically lightning quick. Only the results that have to be displayed inside the Pivot Table are materialized.
2. Before the calculations are performed, Pivot Tables filter your data model. This reduces the number of calculations necessary by requiring Measures to be calculated only across the filtered subset of data.

 

Disadvantages of using Measures

Slowed Performance: Table and Matrix Visuals

A measure is calculated on the fly, using the CPU’s processing power as well as temporary memory, so it’s understandable that it would take some time to complete the computation. Nevertheless, you may first notice a performance issue with Table and Matrix. Why?! The fact is that in the filter context, measures are calculated based on a set of values.

It is only calculated once in Card Visual when the calculation occurs. When it occurs in a column or bar chart, it only occurs for a few elements. When using a table, particularly a matrix graphic, you want to show the details, so you add a field with a lot of values. As a result, you wind up with thousands, if not millions, of calculations, which slows down your report.

The precise context of fields in these visuals causes thousands, if not millions, of calculations when you put the measure calculation in a matrix or table visual. Please note that this would still be extremely quick for simple calculations or calculations performed in a properly constructed model. Even with a large number of records and columns. When the measure calculation does not work properly on its own, the problem arises.
 

Power BI Summation of Measures vs. Columns

 

 

Conclusion

To conclude, In power bi, the choice of when to utilize a measure vs. a column is a little more complicated, as measures are aggregations calculated during each visual interaction, whereas columns are row-level expressions assessed during data load and refresh.As a result, measures should be taken into use for Ratios and aggregations (Profit Margins, Percentage of Variation), Expressions that are responsive to the context of a specified filter. Columns are more appropriate for Static and row-level expressions, attributes, and categories.
 

Frequently Asked Questions

• How do you convert a measure to a calculated column in Power BI?

Step 1:  Pick the measure column that you want to convert into a calculated column (do not tick the checkbox to include the column in the sheet; only select the column).

Step 2: In the “Default Summarization” selection, any aggregation will be applied by default.

Step 3: From the drop-down menu, choose “Do Not Summarize.”
 

• What is the use of a calculated column in Power BI?

Users can use calculated columns to add new data to an existing table in your model. Rather than querying and loading values into your new column from a data source, users can specify the column’s values using a Data Analysis Expressions (DAX) formula.
 

• What happens when you use an aggregation function in a calculated column?

Users can create aggregations in a calculated column that use the current row context to obtain related rows from another table, then sum, count, or average the values in the linked rows.
 

• Can a measure be a table in power bi?

A measures table is essentially a placeholder table that serves as a storage location for your measures. It serves as a container for our measurements rather than containing any actual data.