How to calculate profit margin in Microsoft Power BI

Profit drives companies, big and small, so you’ll most likely need a visual that displays your company’s profit margin effectively. The good news is visualizing profit margin is easy in Power BI if you have all of the information you need. The bad news is there’s no single solution for calculating profit margin, because every company is different. Knowing the logic behind your organization’s business rules is a must if you want to create the perfect calculation(s).

SEE: Hiring kit: Microsoft Power BI developer (TechRepublic Premium)

In this tutorial, I’ll show you how to add a calculated column using DAX to show the profit margin in an Adventure Works visual. I’m using Microsoft Power BI on a Windows 10 64-bit system. You can download the demonstration .pbix file, Adventure Works 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 final results, check out this demo file.

Jump to:

How to find the right profit formula

Before we get started with calculating our profit margin, let’s make sure we understand what we’re calculating. For starters, there are three types of profit to consider:

• Gross margin: Represents our formula, which subtracts costs of goods and doing business from revenue.
• Operating profit: The result of subtracting all operating costs from the gross margin.
• Net profit: The result of subtracting all taxes and financing costs from operating expenses. This result is the most comprehensive when evaluating a company’s profit or profitability.

Adding profit to a visual, when asked, will require a bit of sleuthing on the designer’s part, unless the person asking supplies all needed data upfront. It’s unlikely that most designers would have ready access to the underlying details — much of it is considered confidential. However, much of this data will be in the source database already; you just might need permission to access it.

In its simplest form, profit equals the amount of revenue left after all expenses are paid. That includes salaries, shipping, manufacturing, purchasing and so on. The simple formula:

Profit = Sales − Expenses

is relevant only when Expenses includes all expenditures. If you want a percentage, also known as the profit margin, you’d use this formula instead:

(Sales − Expenses)/Expenses

As a simple example, if a small company reports $250,000 in sales and expenses of $200,000, they have a profit of $50,000, or a profit margin of 25%.

With that background information in mind, let’s examine Adventure Works, our demonstration file, for existing profit data.

How to evaluate data for profit margin calculations

You’ll begin by checking the available data in the underlying table(s). Our sample data already has a calculated column that evaluates revenue and expenses, so let’s explore that expression first. Figure A shows a table visual to keep things simple. The calculated column, Profit Amount, uses the following DAX expression:

Profit Amount = Sales[Sales Amount] − Sales[Total Product Cost]

Figure A

The raw profit value is available in the underlying data, so all you need to do is include it in a visual. As a calculated column, this value is available in the model, so updates are dynamic. The calculated column has all of the same benefits as a regular column.

Here’s the basic rule: Power BI applies a calculated column expression to all rows in the table but evaluates only values within the same row. There’s no aggregating function; Power BI adds the resulting values to the model and calculates it before a filter is engaged.

When everything is so neat and tidy, you’re in luck! As you can see in Figure B, the Sales table in the Data window stores a lot of information. In fact, it has everything you need except a profit margin value.

Figure B

What’s not quite clear, but is important to know, is that the expression is DAX, a formula expression language used in Analysis Services, Power BI and Power Pivot in Excel. This language includes functions, operators and values.

Now, let’s add a calculated column that returns the profit margin.

How to return the profit margin using a calculated column

Let’s take a look at the first record in the Sales table (Figure B):

• The total costs are greater than the income.
• This order comes with a loss of $33.83.

We don’t need to do a thing because the calculated column, Profit Amount, is already available in the model. However, what we don’t see is a profit margin, so let’s add a calculated column to the Sales table to return the profit margin.

To do this, return to the Report window, and build a new table visual on the Sales table; include only the Profit Amount and Sales Amount columns from the Sales table and Sales Order from the Sales Order table. This last field isn’t technically necessary, but we want to see the profit margin for each order rather than an aggregate return based on all orders.

To make this most effective, we’re adapting a bit to the existing data. Because the profit amount already exists, we don’t need the full formula discussed at the beginning of this article. We can instead drop the first subtraction expression and reference the Profit Amount column:

Profit/Expenses

The downside of using this simpler formula is the dependency on the Profit Amount calculated column. Should someone remove that calculated column or make alterations to it, the new profit margin column will return an error.

I want to stress how important it is for Power BI designers to be familiar with the data. There’s no replacement in situations such as this. If you don’t take the time to review existing data, you might repeat what’s already available, which can lead to a convoluted mess down the line.

Add a calculated column to return profit margin as a percentage

To add a calculated column that will return the profit margin as a percentage number, take the following steps:

1. Select the table visual.
2. Click the Table Tools tab.
3. Click New Column.
4. In the formula bar, enter the following expression (Figure C): % Profit Margin = DIVIDE([Profit Amount],[Sales Amount]).

Figure C

As you can see, Power BI adds the calculated column, % Profit Margin, to the Sales table. With the table visual still selected, check % Profit Margin in the Fields pane to add this column to the visual (Figure D).

Figure D

This newly calculated column displays the profit margin for each order; that’s why the visual includes the Sales Order column from the Sales Order table. You can change that column to any other related column that returns profit margin in a meaningful way.

Additional resources

Using a calculated field makes sense when you want the profit margin to be part of the model. In some circumstances, you might prefer measures. In a future article, I’ll show you how to use measures to achieve the same values.

SEE: Hiring Kit: Database engineer (TechRepublic Premium)

This current tutorial hinges on prior knowledge of DAX and calculated columns. If you’re not familiar with DAX, consider reading How to tackle DAX basics in Microsoft Power BI before working through this tutorial. In addition, you might want to read How to add a calculated column in Microsoft Power BI if you’ve never worked with them before.

Read next: Best business intelligence tools (TechRepublic)