Sometimes the data you analyze does not contain the field required for the desired results. Calculated columns come into play here.
Calculated columns employ Data Analysis Expressions (DAX) formulas for various operations, including defining column values, combining text values from multiple columns, and calculating a numeric value from other values. Assume you have the City and State fields in your data but want a single Location field that includes both. This is precisely why calculated columns exist.
Measures and calculated columns are based on DAX formulas, although their usage differs. Measures are commonly used in the Values field of visualization to calculate results depending on other fields. Calculated columns are used as new Fields in visualizations' row, axis, description, and group fields.
When you construct a simple table visual in Power BI Desktop, and all of the entries have the same value, you can see that there is no relationship.
How to Create a Calculated Column?
A calculated column can be created in Report View in two ways:
- Modeling Tab > New Column
- In the Fields field, right-click on the table to which you want to add a new column, then select New Column.
In a Data View, there are three ways to create a calculated column:
- From the table tool tab, choose New column.
- In the Fields field, right-click on the table for which you want to add a new column and then choose New Column.
- Right-click any column and choose New Column.
The New column window appears after you complete the preceding steps. The field where we will write the Dax function is opened in this window, and our newly created empty column is added to the last column, which is named Column by default.
We give our Dax Function a name and then tell it what action we want it to take for the new column. When we confirm the operation, we can see that our new column's name has changed and the calculated values have been inserted in the rows.
In this example, we name our column Total Units Price because we will find the Total of our Unit Sold column.
After writing the Dax function SUM, which represents the sum, we specify the Column that will be used for calculation.
After confirming the operation, you can see that our column name has been updated, as have the column values.