What's the difference between calculated columns vs measures?
- DragonViz
- Apr 21, 2021
- 2 min read
Updated: May 31
The most commonly asked question is related to the difference between calculated columns vs measures:
Key Differences are the following:
Calculated columns = row-level evaluation (row by row calculations)
Calculated measure = aggregate values many rows
Calculated measures aren't stored onto the table/db you created, they are calculated at the time when you trigger the query. Generally, measures are refreshed quicker than calculated columns.
Where can you find these calculated fields on Power BI?
One place you can create a calculated column or measure is under the "Fields" section. After you hover over for "More options" top right of your table, click the drop-down, and you will see "New measure" and "New column" options as below image:
Another location where you can find these fields is when you click on the data tab (circled in the below image). Under the "Calculations" section.
How to calculate revenue and price range using columns?
Sample Data -> Click Here
From this sample data, create calculated column for revenue by the following formula:
Revenue = 'restaurant-1-orders'[Quantity]*'restaurant-1-orders'[Product Price]The Revenue variable will populate in yellow highlight as shown below:
Create another calculated column with this formula for the price range.
Price Range = if('restaurant-1-orders'[Product Price] < 2, "Cheap!", if('restaurant-1-orders'[Product Price] < 10, "Okay Price", "Pricey, but yummy!"))Add these five variables into the Table Visualizations as below:
Adjust the new revenue column into currency ($) by going back to the data tab to reformat:
How to use measure in Power BI?
Create calculated measures with the following formulas:
Total Pricey = Calculate(sum('restaurant-1-orders'[Revenue]), 'restaurant-1-orders' [Price range] = "Pricey, but yummy!")
Total Okay Price = Calculate(sum('restaurant-1-orders'[Revenue]), 'restaurant-1-orders'[Price range] = "Okay Price")Total Cheap = Calculate(sum('restaurant-1-orders'[Revenue]), 'restaurant-1-orders'[Price range] = "Cheap!")As shown above, these measures contain "Revenue" and "Price Range" as part of the formula (column values that were calculated earlier in the tutorial). In summary, measure is a calculation of the aggregate row(s).
Please leave comments or questions below this post. Let us know the topics that you would like for us to cover.
Topics covered:
Comments