Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# Calculating Percent Profit Margins Using DAX In Power BI

by | 9:00 am EDT | September 10, 2019 | Business Intelligence, DAX, Power BI

Working out your profit margins in Power BI with a basic data set can seem like it requires a few steps. You may watch the full video of this tutorial at the bottom of this blog.

Maybe you think you need to use calculated columns to get the result.

Well, you certainly don’t need to do that. There is a much simpler way.

Using measures, you can start with simple information like price and costs, and then via ‘measure branching’, you can work your way up to a profit margin figure that can be filtered by anything in your data model.

This is what we’re going to focus on in this article. After reading this post, you will surely be amazed by how useful this simple process can be in your reports.

## Calculating Percent Profit Margins In Power BI

Before we actually start working out our profit margins, let us have a quick look at our underlying sales table so that we know what we are working on.

As you can see, we have the order quantity, the unit price, total unit cost and total revenue.

With these, we can create our simple measures that will eventually enable us to calculate our percent profit margins.

So let’s dive into the process.

### 1. Total Sales Measure

The first thing we need to do, obviously, is to create a simple measure. In this particular example, let’s work out the total sales which is a sum of the total revenue column.

### 2. Calculating Total Costs Using Iterating Function

Since we need to know what our total profits are, the next thing we need to find out is the total costs. We can do this by using an iterating function.

We don’t have a total cost column in this case so we’re going to use SUMX to compute total cost.

Using SUMX, we’ll go to the sales table and multiply the unit cost by the sales quantity for every single row in that table.

Let us first double check our total costs formula before moving on to the next step.

So let’s create a table using our measures.

We’ll bring in our date, total sales for every single day, and total cost for every single day.

This is the table that we’ll be able to create.

### 3. Total Profits Measure

Now that we have our total sales and total costs measures, we can now calculate our total profits. To get this, of course, we simply subtract total costs from the total sales.

We’ll just drag total profits measure into our table and then we get a total profits column.

### 4. Calculating Margins

We can very easily calculate our margins after those first three simple steps. And of course, there is definitely no need to go back to our data table and add any columns.

What we need to do, again, is to simply create a new measure.

Let us call this % profit margins.

For the formula, we’re going to use DIVIDE, total profits by total sales, and then put in zero as the alternative result.

Let us then drag this measure into our table to see our profit margins per day.

So we’re able to work out our percent profit margins using measures.

This is a very simple and quick process but the output undoubtedly has many uses.

## Uses Of Percent Profit Margins Calculation

Let us now look at the different ways that we can use percent profit margins in Power BI reports.

### 1. Profit Margin Per Customer

First, we can look at the profit margin per customer.

We’re going to drag in profit margin then add the customer names.

We now have a table that shows the profit margin per customer.

If you need a visualization, of course that’s also possible and you can convert the table into a visual very easily.

### 2. Profit Margins Over Time

We can also look at our profit margins over a time period.

What we can do is just copy and paste the table with the percent profit margin per day.

Then we can convert this table into a line graph.

Now we have a visual that shows the percent profit margins over time.

### 3. Comparing Margins Per Product

Using our percent profit measure, we can also see what is our highest and lowest margin products.

Let’s copy and paste our visualization with profit margin and customer names.

Then we’re going to add product name and then turn on data labels.

Now we can compare profit margins by product with Product 14 as the highest and Product 13 as the lowest.

### 4.  Looking At Margins Dynamically

With all the visualizations that we have created, we can look at our margins dynamically.

If we click into our Product 14, for example, we can see how things have tracked over time dynamically for this specific product as shown in the visual number 3.

We can also see how much of the profit margin per customer is product 14 in the first visualization.

Then the visualizations will show the different margins as we click on the different products.

Develop Advanced Scenario Analysis Models Using DAX in Power BI
Are Margins Expanding Or Contracting? Solve in Power BI
Analyze Margin Changes Overtime – Analytics with Power BI and DAX

## Conclusion

In this post, we have seen how to work out our profit margins in Power BI using measures.

The key takeaway here is not actually how to exactly work out profit margins, but about how we get there.

The layering of measures on top of each other allows you to create these branches. They can produce quite complex results, but your formulas remain relatively simple as you step through to the end number.

There are so many ways you can use this – not only with the profit margin number, but also with aspects like percentage growth, time comparison results, and much more.

Starting with your simple core measures is certainly the best way to develop your models. It makes bringing in more advanced techniques like time intelligence and scenarios analysis that much easier.

Calculating How Much Profit We May Be Losing Out On – Power BI Analytics

Cheers,

Sam

## Data Analytics Case Study Guide 2023

Data analytics case studies reveal how businesses harness data for informed decisions and growth. For...

## How to Input a List in Python: User Guide with Examples

In Python, data structures like lists are fundamental. Understanding how to input and manage them...

## How to Count Vowels in a String in Python: 8 Top Ways

Ready to count some vowels in a string? Good, because we are going to the best ways to go about it....

## Machine Learning Engineer Vs Software Engineer

If you're planning to pursue a career in the tech industry, it's crucial to comprehend the different...

## How to Create a List of Tuples in Python: 8 Top Methods

As a Python programmer, you'll often need to create a list of tuples, a list where each element is a...

## Top 10 Free Datasets to Analyze

In today's data-driven world, accessing reliable and diverse datasets is essential for any analyst or...

## 10 Best Interesting Datasets in Different Industries

Data is an incredible tool, especially when presented in a way that's easy to understand. The Content...

## How to Append to a String in Python: 7 Best Ways

When programming in Python, you might encounter situations where you need to modify or add new content...

## 10 Best Free Datasets for Analysis

Are you looking for free, high-quality datasets to analyze and play around with? Data analysis is an...

## Top 10 Regression Datasets: Linear Regression Practice

Are you searching for the best regression datasets to practice and hone your data analytics skills?...

## Can Excel Send Email Reminders? Top 3 Ways Explained

Do you want to send reminder emails based on an Excel Table to keep track of upcoming deadlines, manage...

## Can Excel Open JSON? A Quick How-To Guide

Yes, Excel can open JSON files, with the latest versions of Excel being even more capable. By using...