DAX Difference Between Measure and Column + Example on Report

Last modified date:

We can create two types of new values using DAX in Power BI or PowerPivot. They are calculated measures and columns. Difference between measure and column and its understanding is another major step in order to understand DAX language for Power BI.

New measure or column in Power BI is added by clicking right mouse button above any column. Then select New measure or New column (as seen in the picture).

Column – adding new column into which is later inserted the DAX formula results in the formula being processed on the table level row by row. All column values get summed up on the report level. Created column can then be seen directly in the table.

Measure –  The effect is different with measures. We do not see it by the table as a new column but as a field in the selection below the table. In report, the measure is calculated on the report level in the context in which we display it.

Measure vs. Column With Report Example

This table should help to understand the difference. We will work with these tables during the demonstration.

  • Sales (FactInternetSales)
  • Teritories (DimSalesTerritory), in which the sales occur – geographical information on sales. Meaning where the sales took place.
  • Calendar (DimDate), contains dates and information such as month, year etc.

Step 1) Add new column with total sales into the table with sales (price * amount). It already contains individual item prices, production costs and sold amounts.

difference between columns and measures in dax - example new column

Step 2) We will create simple power bi visualization where I will display running result. Sales will be displayed by territory.

difference between columns and measures in dax - sales by territories

 

Step 3) We will now illustrate the difference between measures and columns. First, add a new column via DAX formula (as in picture below). It will show ratio of unit costs and unit price = UnitCosts/UnitPrice . Then display the result in report.

difference between columns and measures in dax - bad measure example, share as a new column

The result is nonsense (as in the picture) – what happened is that percentage shares got counted up throughout all rows

Step 4) We need to create not a new column but a DAX measure in order to get the report working correctly. Measure is calculated all the way at the report level. Formula for the measure will be similar only with the exception of aggregate function usage. Costs and price ratio – SUM(UnitCosts)/SUM(UnitPrice). The result is exactly as we need it.  

difference between columns and measures in dax - example of a new measure in report

I hope that the difference between measure and column in creating new values was explained well enough.

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: DAX Power BI reporting Tags:

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.