Calculated columns and measures are both created on Power BI using DAX (Data Analysis EXpressions).
How can you find these tools? By:
- #1. Selecting from the dynamic ribbon bar under the Table Tools tab, OR
- #2. Right-clicking on the relevant table in the Data pane on the right
- #3. Alternatively, while in the table view, you can right-click on the relevant column in the table to bring up the options shown in #2 above
Calculated Columns
Sometimes when analyzing data in Power BI, you may find that you need a data field/column that your table doesn't contain. This is where calculated columns come in handy. Calculated Columns are user-defined columns that live inside your table, calculated at the row level.
Calculated columns are what you'd reach for when, for example, you want to put together text values from a couple of different columns to create a Full Name column. Your DAX syntax might look something like:
Where Customer_Purchases_Table is the table name and First Name and Last Name are existing column names.
Here is another example, using a logical function to create a new column that categorizes different types of purchases:
When using calculated columns, a new column is created in the data table, i.e. 'Full Name' and 'Purchase Type' in the examples above.
Measures
Measures (usually aggregations) are used in your visualisation, evaluated within the filter context of the report. Since they're usually aggregations, they're naturally not performed at the row level. And they do not create a new column in your table.
There are 3 ways to create a measure:
- Automatic - This type of measure is created when you drag a column with default summarisation into a visualisation. For example, a 'Revenue' column would default to 'Sum of Revenue' as shown:
- Quick - This is a ready-to-use template for measures that doesn't require any formulae or coding. You can find this option in the dynamic ribbon bar under the Table Tools tab. Selecting it opens up a Calculations dialog box, where you can pick what type of calculation you want to do, as shown:
- Calculated - This is the type where you need to write DAX syntax. For example, you would write a syntax like the one below to calculate the total number of products:
Differences between Calculated Columns and Measures
*Evaluation Context (when are things calculated?)
- Filter Context: A set of filters that are applied to a table or a visual, which affects the results of the calculations and aggregates performed on that table or visual.
- Row Context: Whether the calculation is performed row by row, or whether it is performed after aggregation.
When to use a calculated column vs. when to use a measure
Are they interchangeable? No, as we've see in the examples above. In some cases, however (e.g. calculating the sum of revenue), you can achieve the same outcome with either. In summary, according to Power BI documentation:
You often use measures in a visualization's Values area, to calculate results based on other fields. You use calculated columns as new Fields in the rows, axes, legends, and group areas of visualizations.