How to use measures to calculate profit margin in Power BI
These two measures make quick work of returning profit and calculating profit margin in your Power BI projects.
A previous TechRepublic tutorial, How to calculate profit margin in Microsoft Power BI using a calculated column, shows you how to calculate profit margin as a percentage by adding a calculated column to an existing table. This is a helpful technique for calculating profit margin in Power BI, but sometimes you won’t want to add a calculated column. When this is the case, you can use custom measures to return the profit as both a currency value and a percentage.
In this tutorial, I’ll show you how to use DAX to create two profit measures in an Adventure Works visual. We’ll also discuss why you might choose to use measures instead of calculated columns.
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.
How to know when to use a measure instead of a calculated column
Sometimes it doesn’t matter whether you use a calculated column or a custom measure to return profit values. When it does matter, you’ll want to make an informed decision. Let’s begin with a brief description of both approaches and their best use cases:
Calculated column: The result of a DAX expression that evaluates row by row (row context) and is part of the model.
Custom measure: A calculation that usually works as an aggregate; Power BI evaluates them on the fly, as needed, and results aren’t saved to the model. Read More…
