How to calculate profit margin in Microsoft Power BI using a calculated column
Your email has been sent
Creating a visual that includes profit amount and profit margin is easy if you know the right DAX expressions to use in 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:
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:
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.
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.
Let’s take a look at the first record in the Sales table (Figure B):
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.
To add a calculated column that will return the profit margin as a percentage number, take the following steps:
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.
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)
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays
How to calculate profit margin in Microsoft Power BI using a calculated column
Your email has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
This is a comprehensive list of the best AI art generators. Explore the advanced technology that transforms imagination into stunning artworks.
Find the perfect payroll service for your business without breaking the bank. Discover the top cheap payroll services, features, pricing and pros and cons.
Is NordVPN worth it? How much does it cost and is it safe to use? Read our NordVPN review to learn about pricing, features, security, and more.
Free project management software provides flexibility for managing projects without paying a cent. Check out our list of the top free project management tools.
Australian and New Zealand enterprises in the public cloud are facing pressure to optimize cloud strategies due to a growth in usage and expected future demand, including for artificial intelligence use cases.
Zero-trust security operates on the fundamental premise that trust should never be assumed, regardless of whether a user or device is inside or outside the corporate network. In this TechRepublic Premium guide, we will explore the benefits of incorporating zero-trust security into your organization’s cybersecurity framework to prevent data breaches and strengthen the protection of …
Every organization relies on data that must be protected and backed up in a reliable and secure way by authorized personnel. While the content or criticality of the data may vary, the processes behind a successful backup strategy are universal. This checklist from TechRepublic Premium will help you establish a standard framework for backing up …
Artificial intelligence comes in many varieties, from tools that respond to customers via chat to complicated machine learning algorithms that predict the trajectory of an entire organization. Despite years of activity and the imaginative ideas found in science fiction, AI doesn’t yet comprise sentient machines that reason like humans. Rather, AI encompasses more narrowly focused …
Data governance is no longer a ‘nice-to-have’ measure in organizations as there is a growing concern around the globe that unregulated use of data can result in serious privacy violations, among other issues. That’s why data governance has topped the agenda in many continents as more efforts are being put in place to provide standards …
Get the web’s best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let’s start with the basics.
* – indicates required fields
Lost your password? Request a new password
Please enter your email adress. You will receive an email message with instructions on how to reset your password.
Check your email for a password reset link. If you didn’t receive an email don’t forgot to check your spam folder, otherwise contact support.
This will help us provide you with customized content.
Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.
Leave a Reply