# Calculate Percentage Margin In Power BI Using DAX

Today, I’m going to do a quick and easy tutorial on how to calculate one of the most commonly used metrics, especially if you’re dealing with sales, revenues, or transactions. We’ll calculate the percentage margin. I’m going to use profit margin as an example here, but this technique doesn’t have to be always related to profits; it could be any sort of margin. You can watch the full video of this tutorial at the bottom of this blog.

## Using The Waterfall Technique

Let’s jump to the model first. We want to make sure that it has been set up in an optimized way. I know that Microsoft formats the model using a star schema. Personally, I’m not very fond of it. Instead, I use the waterfall technique, which is sometimes called the snowflake technique.

This technique is where the filters flow down to your fact table from your lookup table.

Let’s have a quick look at our Sales table. As you can see, there’s no way to create the percent profit margin because there are no profit numbers in the table.

When they’re starting out with Power BI, most users will create a calculated column, calculate the profits, and then from there, work out the profit margin.

## Calculating The Total Sales

I’ve created a simple measure called Total Sales which sums up the Total Revenue column. Even if you’re dealing with something totally different like HR data or marketing data, the techniques I discuss are reusable across any industry and business function.

The examples I will show use the measure branching technique, where we start with our core measures and then branch out into other measures like margins.

## Calculating The Total Costs

With measure branching, we start off with a core measure like Total Sales, and then create another measure called Total Costs. In this measure, I’ll use SUMX which enables me to do calculations at every single row of a table. It will iterate through every single row of the table I specify, which in this case is the Sales table. For every row, I will multiply Quantity by Total Unit Costs.

Remember that in the Sales table that we just looked at, there was no actual Total Costs column. There were only these two columns. This is why I needed to do multiplication at every row, and then sum up the results. This is what SUMX and all the iterating functions do.

We now have Total Sales and Total Costs in our table.

## Calculating The Total Profits

I can create another really simple measure called Total Profits. This is where measure branching comes in. I’m going to simply branch out again and find out the difference between Total Sales and Total Costs.

I’ve also placed the Total Profits in my table.

## Calculating The Percentage Margin

To calculate the percentage margin, I will create another measure. I’m going to use a function called DIVIDE to divide the Total Profits by the Total Sales, and I’m going to put an alternative result of zero.

We’ll also turn this into a percentage format.

We can now see the percentage margin.

Some of you might ask why we didn’t do this using just one formula. My recommendation is to branch out slowly and start from the simplest measures before you create the more complicated ones. Think about how easy every single measure was that we worked through when we build it step by step. It’s easier to audit when you’re able to break things out in a table and be able to look at the results and double-check the numbers.

## Visualizing The Data

Once I turn this table into a visual, it’s a bit busy and all the data is similar when you look at the customers.

If you want your visualization to stand out, the best way to showcase this is with conditional formatting, especially when you have a lot of data points that are quite similar.

You can change the background color and use two contrasting colors. You can go from light to dark blue.

Another thing you can do is change what you showcase in the axis and start at 30%.

You can now see more variability in the visualization. Obviously, you just need to make sure that your consumers know what they’re looking at.

Another way to show this is to break out your top 5 advisors or your bottom 5 stores.

Calculating Percent Profit Margins Using DAX In Power BI
Calculating Dynamic Profit Margins â€“ Easy Power BI Analysis With DAX
Use Power BI Measure Branching To Check If Your Margins Expand As Revenue Grows

## Conclusion

With this tutorial, I hope you’ve learned that when you calculate the percentage margin, you need to make sure that you start off in the right place before evolving into the more advanced calculations and visualizations. This tutorial goes to show how you can tell really good and compelling stories using raw data in Power BI.

Sam

## Debugging DAX: Tips and Tools for Troubleshooting Your Formulas

One of the main reasons why businesses all over the world have fallen in love with Power BI is because...

## Practical Application of TREATAS Function in DAX

A hands-on project focused on using the TREATAS function to manipulate and analyze data in DAX.

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...