Image: dennizn/Adobe Stock Not every piece of info you need will be in a table. For example, if you wish to know the revenue of a product you sell, that value probably isn’t stored at the table level. Instead, you need to use an expression that subtracts the cost of production and/or distributing the item from the cost of the product. Normally, you do not store the result of an estimation in a table. Rather, you use an expression to return the profit when needed.
In this tutorial, I’ll show you how to create a calculated column to return crucial details that’s not otherwise saved at the table level. You can then add the column to visualizations or produce new ones based upon the brand-new column.
Jump to:
I’m utilizing Power BI Desktop on a Windows 11 system utilizing Microsoft Material. You can use Power BI Service, and Material isn’t required; nevertheless, there may be subtle differences in between the instructions and screenshots.
You can download the demonstration.pbix file, AdventureWorks Sales from GitHub. Once downloaded, double-click the.pbix file to open it in Power BI and follow along, or use a.pbix file of your own. If you want a sneak peek at the results, check out this demo file.
How to decide between a computed column and a measure in Power BI
Must-read big data coverage
If you’re familiar with Power BI steps, you might be questioning the distinction in between those and computed columns. Both are based on Data Analysis Expressions. The primary distinction is that you typically add a procedure to a visualization by including it to the Values pail. In contrast, a calculated column is a new field at the table level that can be contributed to rows, axes, legends and groups.
SEE: Here’s how to include quick steps for complicated computations in Microsoft Power BI Desktop.
Determined columns and steps may appear interchangeable, and often, it will not matter. When choosing which to utilize, context is the identifying factor:
- Power BI utilizes steps with visualizations and updates them after a filter is used. The formula often includes an aggregate function to assess groups.
- Power BI uses a calculated column expression to all rows in the table, but evaluates only worths within the exact same row. There’s no aggregating function. Power BI adds the resulting worths to the model and determines it before a filter is engaged.
Now that you’ve got a concept of what computed columns can do, let’s develop one.
How to include a calculated column in Power BI
When including a calculated column, you must utilize related information if you’re working with more than one table. In some cases all of those values will remain in the exact same table, so the relationship will not be a factor. We’ll use a calculated column to return an easy revenue margin based on 2 fields in the demonstration.pbix file’s Item table. When applying this to your own work, make sure to look for Relationships in the Design window if you’re working with two or more tables.
To add a calculated column to the Item table:
- Right-click Products in the Fields pane and select New Column. Power BI names the brand-new column “Column” by default, and Power BI will open the formula bar in reaction.
- In the formula bar, overwrite “Column =” by getting in Basic Earnings Margin = Product [Sticker price] – Product [Requirement Cost]
- Click the checkmark to the left to add the brand-new column (Figure A). Power BI includes the new column to the Fields pane.
Figure A
Include a calculated column to the Item table in Power BI.
Figure B
). Figure B Include the calculated column to the table visualization. This easy formula deducts Standard Expense from the List Price. Remember, if you base a visualization on the Product table and include the new column to it, Power BI will calculate the profit margin before the user clicks a filter.
In addition, Power BI stores the profit values in the design, so they are
readily available to other visualizations. Including a determined column with a portion in Power BI The Basic Profit Margin computed column does not consider any applied discount rates and many other aspects. It is, as named, a” simple “profit margin. As it is, viewing these values does not help us much, but a percentage would.
Let’s add another calculated column that will return the percentage of profit for each item. Seeing the easy profit as a portion will be more practical. To do so, repeat the procedure above, utilizing the formula shown in Figure C:
Basic Earnings Percentage = (‘Item’ [List Price] – ‘Item’ [Requirement Cost]/ ‘Product’ [Sale price]
Figure C
Get in the column’s formula, and reformat the outcomes. To change the format for this column from currency to portion, pick Basic Earnings Percentage in the Fields pane, and then select Portion from the Format dropdown in the Formatting group on the Column Tools tab.
SEE: Here’s more on how to determine revenue margin in Microsoft Power BI using a determined column.
The addition of this column may change the sort order, but do not worry about it. Both determined columns are basic in structure, but they return helpful details. Revenue margins run from 23% to over 64%. This is far better details than the currency profit returned by the first calculated column. Fortunately, Power BI can manage much more complex solutions.
How to produce a calculated column that uses an IF function
A calculated column evaluates a returned value for every record in the table; nevertheless, that does not imply you won’t wish to filter those results. Thankfully, it’s easy to aggregate the lead to a conditional manner for more filtering possibilities.
To illustrate, let’s include a calculated column that notes whether the earnings margin is below or above 40%. To get going, repeat the process for adding a new column to the Product table and go into the following formula:
BenchmarkProfit = IF((Product [Sticker price] – Product [Requirement Expense]/ Product [Market price] Include a calculated column that references a field in another table. You’ll discover that the formula uses the associated function to reference the table that isn’t in the active
table, Sales 
. This function referrals a field in an associated table and returns a value
by examining the existing row. That’s it, however there is one requirement: This function requires a many-to-one relationship between both tables. If no relationship exists, you must create one or discover another option. Fortunately for us, this relationship currently exists. Source
