In this Power BI Tutorial, we will discuss what are calculated columns in Power BI? What are measures in Power BI and what is the difference between the calculated column and measure in Power BI?
Before understanding the difference between a measure and a calculated column, let us understand first what is calculated columns and what are measures in Power BI?
Calculated Column in Power BI
We can create a new column to a table in the data model in the Power BI desktop. We can create a column by defining a DAX expression which will evaluate row by row in the entire table.
To create a column in the Power BI desktop, click on New column like below:
Then you can write a DAX expressions like below:
Total Cost = Sales[Price]*Sales[Quantity]
The above DAX expression will calculate values by multiple the two columns Price and Quantity column from the Sales table.
You can see the output like below:
If you will add the data in a Table visualization, the Power BI visualization will like below:
In the calculated column, the calculation is done row by row in the rable.
Another thing, we need to remember that, calculated column stores values in memory like other columns.
The calculation is done when refreshing the report in Power BI desktop.
Measure in Power BI
Now we will see what are measures in Power BI.
Measures are used to calculate aggregates like SUM, SUMX, average, calculate, etc. Measures are calculated at the time of query and they are not stored in the database.
Measures are not stored in the memory, so these are faster. Click on New measure like below:
Write the formula like below:
Total Revenue = SUM(Sales[Total Cost])
If you will add a Card visualization in Power BI, you can see the Power BI measure value like below:
If you need to operate on aggregate values instead of on a row-by-row basis, you must create measures.
When to use Calculated Column and when to use Measure?
Here the question comes as to when to use the calculated column and when to use measures in Power BI?
This depends on the requirement.
If you want to calculate row by row, then create a calculated column in Power BI.
If there is a requirement for aggregation or if you need filter criteria for the report, then you can create a measure in the Power BI.
You may like following Power BI tutorials:
- Power bi free vs pro vs premium
- How to use weekday function power bi with example
- Power BI convert hours to minutes
- Contact your admin to enable embed code creation Power bi
- Get Month Name from Month Number in Power BI
- Power BI convert yyyymmdd to date
- Power BI Calculated Column Example
- How to use Power BI sync slicers
- How to Create Report in Power BI from SharePoint List
- Power bi conditional column example
- How to split column in Power bi
- How to change data source in Power Bi
- Power BI Group By Examples
- How to create and use Power BI Bookmarks
This Power Bi tutorial we learned:
- What is a calculated column in Power BI?
- What are measures in Power BI?
- Difference between a calculated column and measure Power BI
- When to use Calculated Column and when to use Measure?
I am Bijay a Microsoft MVP (8 times – My MVP Profile) in SharePoint and have more than 15 years of expertise in SharePoint Online Office 365, SharePoint subscription edition, and SharePoint 2019/2016/2013. Currently working in my own venture TSInfo Technologies a SharePoint development, consulting, and training company. I also run the popular SharePoint website EnjoySharePoint.com