In this post, you’ll learn how to create a real-world Power BI Profit Margin report.
Power BI Profit Margin Calculation
This section describes a simple profit margin calculation.
Profit margin is the ratio of total profit over total sales.
Total sales are calculated by multiplying the number of units sold by the price at which they were sold. If you sold 3 widgets at $2.00 each, then your total sales is $6.00.
Total profit is the total sales minus the total cost. Total costs are calculated by multiplying the number of units sold by the price those units cost. If you sold 3 widgets, and they cost you $1.00 each, then your total cost is $3.00. Your total profit would then be [Total Sales] – [ Total Costs] = $6.00 – $3.00 = $3.00.
Finally, the profit margin would be $3.00 / $6.00, or 50%.
Review the Report Data
I used a Microsoft Excel workbook in this example. The workbook contains a few tables, but the most important are the Sales table and the Products table. The former contains a history of all sales made, and the latter contains the product name and product cost dimensions.
Power BI Sales Data
Refer to Order ID OID12855 and the previous formula. The total sales for that line item is 1 quantity at $530.00, or a total of $530.00.
Power BI Product and Cost Data
PID2011 is the product sold in OID12855 above. This product costs $435.00 / unit. So, in a sale of one unit, the total profit would be $530.00 – $435.00 = $95.00.
Power BI Profit Margin Calculation
From the definition above, profit margin is $Profit / $Sales. As a result, the single line profit margin on one unit of PID2011 is $95.00 / $535.00 = 18%.
Power BI Profit Margin Measures with DAX
Finally, we see how to convert the above into Power BI measures using DAX.
Calculating Total Sales Using SUMX
Calculating Total Cost Using SUMX and RELATED
Calculating total cost for the Power BI Profit Margin report is a bit more complex than calculating total sales in this example. The product cost is not included in the same table as the sales information. Therefore, the DAX RELATED function is needed to pull the cost of the sales’ items product from the Product_Data table. For reference, here is the data model showing how the AllSales_Data and Product_Data tables are related:
Calculating Total Profit – Simple
Calculating total profit is simple given that the total sales and total costs measures are already created. Add a new measure, MTotalProfit = MTotalSales – MTotalCost:
Power BI Profit Margin with DAX DIVIDE
Finally, calculating the profit margin isn’t difficult either because the required measures are already created. The next video shows the calculation and a bit of cleanup in preparation for creating an actual dashboard.
However, do take notice of the DIVIDE function. As a result, the Profit Margin calculation is safe from divide by zero errors.
Next Steps
Following this setup, the report and dashboard will become more interesting and useful to the business. The report will get some time intelligence updates and additional improvements to the user interface next round. For now, here’s where the report stands after some spacing improvements and the addition of a background image:
Categories: Power BI
Leave a Reply