Calculating Percent Profit Margins Using DAX in Power BI

2 comments

Working out your profit margins in Power BI with a basic data set can seem like it requires a few steps. 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.

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.

I’m a big fan of starting with your simple core measures like sales or costs and then working your way up to calculations that branch out from there. It’s certainly the best way to develop your models and makes bringing in more advanced techniques like time intelligence and scenarios analysis that much easier.

Check out this video to learn much more.

***** Learning Power BI? *****
All Enterprise DNA TV Resources
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – Power BI Resources

2 comments on “Calculating Percent Profit Margins Using DAX in Power BI”

  1. Thanks Sam, its a good method to follow with the branching of measures! Just had a question on how you would for example workout the maximum profit over a time period? I know you can get the Max values in a table format but how do you get the max “profit value” over the time period of the measure? Max doesn’t work on measures so is there a alternative you can suggest?

    Like

    1. Hi Garry, I’m understanding your scenario properly you need to solve it with an interating function. Try MAXX( tablename, [Total Profits] ). This should work. You obviously need to have a date table that filters the correct time period for you

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s