Calculated Columns In Power BI

Overview

In this article, we will talk about calculated columns in Power BI. To prepare any report in real life, we must have to prepare calculated columns and measures in Power BI.

In this article, we will learn the following topics.

1. Definition of Calculated Column
2. How to prepare calculated column in Power BI?
3. How do calculated columns consume memory?
4. Advantages and disadvantages of having calculated columns

Now, let’s get started!

Calculated Columns

• Calculated columns are the same as another database column.
• We can prepare a new column using existing columns.
• We can also use DAX expression to prepare a new calculated column.
• A calculated column is applied to every single row of the dataset.

Real Life Scenario for Calculated Columns

• For Finance Organization we can calculate P/L (Profit and Loss) operations using calculated columns.
• For Time based operations like

• To find our month or year from a Date, we can write calculated columns.

Steps to prepare Calculated Column in Power BI.

Step 1

There are two ways to prepare Calculated column in Power BI.

1. Go to Modeling > Create Column.
   
    
   
   
2. Go to Data view > Right click on table > Create Column.

 

Step 2

It will open a blank formula bar to write an expression.

 

Step 3

Now, let’s create a Calculated column which will calculate Total Unit Cost.

So, we will write the following formula.

1. Total Sales Cost = Sales[SalesQuantity]*Sales[UnitCost]
   
   

As we can see, a new calculated column is created.

It is a multiplication of Sales Quantity and Unit Cost.

 

Step 4

We will create some date hierarchy. We will find out the month and the year from the date field.

For that, we will use DAX operation.

 

1. Month = MONTH(Sales[DateKey])
   
   

Now, let’s create a Calculated column for the year.

1. Year = YEAR(Sales[DateKey])
   
   

How do Calculated Columns consume memory?

• When we are creating calculated columns, they are stored in memory the same way other columns are.
• Please check the below screenshot. Total New Cost is created in a new column.
  
   
  
  
• Data is refreshed in a calculated column when we refresh the data model.

Advantages

• Supports DAX Expressions.
• We can prepare new columns using existing data.
• Consumes RAM instead of CPU.
• Supports Row by Row calculation

Disadvantages

• It consumes RAM, so if there are lots of calculated columns, it requires more memory space.
• Data is refreshed only if we refresh the data model.

Conclusion

This is how calculated column works in Power BI. We will discuss measures in my next article.

Stay Connected with Me!