Profit drives businesses, big and small, so you most likely need a visual that effectively represents your company’s profit margin. The good news is that visualizing profit margin is easy in Power BI if you have all the information you need. The bad news is that there is no one solution to calculating profit margin because every company is different. Knowing the logic behind your organization’s business rules is a must if you want to make the perfect calculation(s).
SEE: Hiring Pack: Microsoft Power BI Developer (Tech Republic Premium)
In this tutorial, I’ll show you how to add a calculated column using DAX to display profit margin in an Adventure Works visual. I am using Microsoft Power BI on a Windows 10 64-bit system. You can download the demonstration .pbix file, Adventure Works Sale, from GitHub. Once downloaded, double-click the .pbix file to open and track it in Power BI or use a .pbix file yourself. If you want a taste of the final results, check this out demo file.
Jump to:
How to find the right profit formula
Before we start calculating our profit margin, we need to make sure we understand what we are calculating. To begin with, there are three types of profit to consider:
- Gross Profit Margin: Represents our formula, which subtracts the cost of goods and business from revenue.
- Operating profit: The result of subtracting all operating expenses from the gross margin.
- Net profit: The result of subtracting all taxes and borrowing costs from operating expenses. This result is the most comprehensive when evaluating a company’s profit or profitability.
Adding profit to a visual, when prompted, requires a bit of detective work on the designer’s part, unless the person asking the question provides all the necessary data upfront. Most designers are unlikely to have easy access to the underlying details – much of it is considered confidential. However, much of this data will already be in the source database; you may just need permission to access it.
In its simplest form, profit is equal to the amount of revenue left after all expenses have been paid. That includes salaries, shipping, production, purchasing and so on. The simple formula:
Profit = Sales − Costs
is relevant only when Expenses include all expenses. If you want a percentage, also known as the profit margin, use this formula instead:
(Sales − Expenses)/Expenses
As a simple example, if a small business reports $250,000 in sales and $200,000 in expenses, they have a profit of $50,000, or a 25% profit margin.
With that background in mind, let’s examine Adventure Works, our demo file, for existing earnings data.
How to evaluate data for profit margin calculations
You start by checking the available data in the underlying table(s). Our sample data already has a calculated column that evaluates income and expenses, so let’s look at that expression first. Image A shows a table visual to keep it simple. The Profit Amount calculated column uses the following DAX expression:
Profit amount = Sales[Sales Amount] − Sale[Total Product Cost]
Image A
The raw profit value is available in the underlying data, so you just need to include it in a visual. As a calculated column, this value is available in the model, so updates are dynamic. The calculated column has the same advantages as a regular column.
Here’s the basic rule: Power BI applies a calculated column expression to all rows in the table, but only evaluates values within the same row. There is no aggregation function; Power BI adds the resulting values to the model and calculates them before enabling a filter.
If everything is so neat and tidy, you’re in luck! As you can see in it Figure B, the Sales table in the Data window contains a lot of information. In fact, it has everything you need except a profit margin value.
Figure B
What’s not entirely clear, but 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 look at the first record in the Sales table (Figure B):
- Total costs exceed revenue.
- This order is associated with a loss of $33.83.
We don’t need to do anything 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 a total return based on all orders.
To make this as effective as possible, we adapt a bit to the existing data. Since the win 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 refer to the Profit Amount column:
Profit/Expenses
The disadvantage of using this simpler formula is the dependency on the calculated Profit Amount column. Should someone remove or make changes to that calculated column, the new profit margin column will throw an error.
I want to emphasize how important it is for Power BI designers to be familiar with the data. There is no replacement in situations like this. If you don’t take the time to review existing data, you may be duplicating what’s already available, which could lead to a complicated mess later on.
Add a calculated column to return the profit margin as a percentage
Complete the following steps to add a calculated column that returns the profit margin as a percentage number:
- Select the table visual.
- Click the Table Tools tab.
- Click New column.
- 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 % Profit Margin calculated column to the Sales table. With the table visualization still selected, check % Profit Margin in the Fields pane to add this column to the visual (Figure D).
Figure D
This newly calculated column shows the profit margin for each order; therefore, the visual contains the Sales Order column from the Sales Order table. You can change that column to any other related column that returns the 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 may prefer measures. In a future article I will show you how to use measures to achieve the same values.
SEE: Rental kit: Database engineer (Tech Republic Premium)
This current tutorial is based on prior knowledge of DAX and computed columns. If you’re not familiar with DAX, consider reading Tackle the basics of DAX in Microsoft Power BI before working through this tutorial. In addition, you may want to read Add a calculated column in Microsoft Power BI if you have never worked with them.
Read more: Best business intelligence tools (TechRepublic)
