How to Visualize Product Profitability using Microsoft Power BI
A step-by-step guide from data to dashboards and insights
“You cannot manage what you cannot measure” — Peter Drucker
By measuring and quantifying results, businesses are able to understand how they are doing and make better informed decisions that will lead to more growth or profitability.
In this tutorial, we will cover why measuring and calculating product profitability is important, how profitability is calculated, how we can use Microsoft Power BI in generating a dashboard and how we analyze the data and interpret the results.
So let’s dive right in!
Table of Contents:
- Why is calculating product profitability important?
Having visibility of the profitability of the products we sell is important. We need to answer the important question: Out of every dollar we sell, how much of that covers our cost and how much generates profit? Knowing that number is powerful, especially when placed in context (which we will discuss in the latter part of this article).
Being profitable allows a business to reinvest those profits back to its operations for continued growth, improve existing products and develop new products, pay its investors and shareholders dividends, and have a safety buffer when economic conditions change.
2. How do we calculate it?
Revenue is the amount a company earns in return for its products or services.
Whereas, direct cost covers all costs that are directly related in producing the product or service — raw materials, labor costs, depreciation costs of the machinery used etc. These do not include those which are not directly related like advertising, marketing, or distribution costs.
Gross Profit is the portion that remains after we cover the direct costs of production as highlighted in Figure 2.
There are many different perspectives in measuring profitability in addition to gross profit like net operating margin and net income, but we will focus on gross profit and gross profit % in this tutorial.
To calculate gross profit %, we use the formula in Figure 3.
Let’s say you have a washing machine business which sells its product for $995.00 and direct cost of $350.00 to make. Deducting the cost from the revenue, we get the gross profit of $645 as seen in Figure 4.
To get the gross profit %, we divide the $645 by the selling price of $995 which is 64.82% as shown in Figure 5.
Now that we have the essentials covered, let’s head to using Microsoft Power BI in generating the dashboard.
3. How do we use Microsoft Power BI to generate a product profitability dashboard?
The next part of our tutorial will cover these 4 steps: importing data, transforming data, visualizing data and analyzing data.
Microsoft Power BI is a great tool in visualizing data and communicating information in a way that helps management make informed decisions.
We will be using sample datasets created for a fictional company which are saved in Google Drive as seen in Figure 7. You can download them here, save the files in your computer, and then work on the hands-on solutions as you read along. You can also access the pbix file for the final report. At this point, you might be wondering what a pbix file is. Files with a pbix extension are those which are produced by the Power BI Desktop. It contains all the information including the data, models, formulas, visualizations that were added or created by the user.
In this tutorial, we will use Microsoft Power BI Desktop Version: 2.103.881.0 64-bit (March 2022).
We are now ready to import the data.
First, we load the data into Power BI by clicking ‘Get data’ and selecting ‘Excel workbook’ as highlighted in Figure 9.
This will open a pop-up window. Locate where the files are saved, and select ‘2020 Transactions’. Then, click ‘Open’.
Now that we have loaded the information we need, we can start transforming the data for our use.
The Navigator window will open similar to what we have in Figure 12. Tick the box in front of the ‘2020 Transactions’ and click ‘Transform Data.’
The Power Query Editor will appear. Check how the columns are formatted, and remove any excess columns not needed as highlighted in Figure 13.
Do the same steps for the other 2 files (Product_Details.xlsx and Sales_Managers.xlsx). The Product_Details table contain the price, cost and gross profit for each product, while the Sales_Managers file contain the region that the sales managers support.
For Product_Details, format the ‘Product_Gross_Profit_%’ column as percentage. Click the icon before its name and that will show the drop-down similar to what we have in Figure 14.
Next, let’s create a calculated column called ‘Total_Sales’ in the ‘2020_Transactions’ table. Select ‘Custom Column’ as shown in Figure 15 and type the formula on the formula bar:
Total_Sales = RELATED(Product_Details[Product_Price]) * ‘2020 Transactions’[Quantity]
Since the price of the product is in another table, we need to use ‘RELATED’ in order to call the details. After typing the formula, press the [ENTER] key and it will show the sales value as illustrated in Figure 16.
Click on the Column Tools, and select thousands separator [,] to format the numbers.
Do the same set of steps to calculate the column values under Total_Cost and Total_Gross_Profit.
Lastly, let’s add the ‘Gross_Profit_%’ column by adding another column, and using the formula in Figure 19.
Gross_Profit_% = ‘2020 Transactions’[Total_Gross_Profit] / ‘2020 Transactions’[Total_Sales]
NOTE: It is important to note that we need to follow the syntax of the formula for it calculate correctly.
Now that we have added the necessary columns, we can start visualizing the data.
Click the report icon (as seen in the red box from Figure 20) which shows the empty white canvas.
Then at the right side, you will see a set of icons for the different types of charts or graphs that can be used. Find ‘Card’ which we will use in showing the Total Sales, Total Gross Profit and Gross Profit %.
Then under the ‘Fields’ section, select ‘Total_Sales’ as highlighted in Figure 23.
The Total Sales will show in the report as can be seen in Figure 24.
Do the same steps for the ‘Total_Gross_Profit’ and ‘Gross_Profit_%’. The default used for the fields is ‘sum’ — since this is not appropriate for the Gross Profit %, go to Fields drop-down and select Average.
This will now reflect the correct % of 59.13%.
Next, we will show the sales by product and month. Let’s go and select the ‘stacked column chart.’ In the ‘fields’ column, select ‘Total Sales’ and under the Transaction Date, select ‘Month.’ Then under the Product Details table, select ‘Product_Name’.
The stacked column chart and fields selections will look as shown in Figure 26.
Next, let’s look at the profitability trend per month. This time, let’s select the ‘Line and Stacked Column Chart’.
Do the same steps we did for the ‘stacked column chart’ and this time, let’s add ‘Total_Gross_Profit’ for the line values. Having the line graph allows us to see the gross profit trend each month. See Figure 28 for the settings.
The last visual we will use is a treemap to help us see how sales is composed per product. Select Treemap from the visualizations, then select ‘Total_Sales’ under ‘2020_Transactions’ and ‘Product_Name’ under ‘Product_Details’.
The completed dashboard will look as shown under Figure 30.
It is important to note that we can tweak and select the visualization that we deem appropriate as long as it communicates the information we would like to tell.
4. How do we analyze the data and interpret the results?
Now that we have loaded the data and created the visualization, we can start analyzing and obtaining insights.
We can see that at a high level, the revenue for the year is $233M and the gross profit % is 59.13%. The gross profit % value is useful for a management team of a business since this measures how well the business uses its resources in producing its products or services.
The profit % will more useful and insightful when placed in context — such as comparing it with a prior period, with prior year or with another company in a similar industry.
At this point, we can start asking the following questions:
- How does this compare with last year?
- Did our margins increase or decrease?
- Did anything change in our production process year over year?
- Are there investments in technology that will make our production more efficient and thus have an impact on our costs? And will these investments translate to better quality that our customers will value?
- How do our margins compare with other companies in a similar industry? Are they selling similar products at a higher price? or do they have lower costs in production?
We can analyze the data further by comparing how each product is performing. If we select Refrigerator from the stacked column chart, we can see that it represents a significant amount of our revenue — almost half as can be seen from the treemap (~49%) while gross profit is 62%, compared to vacuum cleaner at $56M revenue and GP% of 50.97% and washing machine with $62M and GP% of 64.82%.
Our highest margin product is the washing machine at 64.82%. However, it only accounts for $62M of our revenue. Taking account of the product mix and increasing the sales for this product will increase the profitability.
On a related note, vacuum cleaners have the lowest margin at 50.97%. They account for about a quarter of the revenue. Consider whether there are any dependencies in selling this product with others, it might be that the customers buy this together with the refrigerator and washing machine.
Having the gross profit % gives us more insight and is a good indicator to see how we are performing compared to others. This helps us take the next steps needed and make better informed decisions.
5. Closing Remarks
That’s pretty much it!
In this post, we were able to discuss why product profitability is important, how profitability is calculated — in this case we focused on gross profit, how we use Microsoft Power BI in generating the product profitability dashboard, and how we analyze the data and interpret the results.
Having this understanding allows us to ask more insightful questions, that lead to better decisions, and lead to more business success.
I hope you found this useful and insightful. For any questions, feel free to reach out to me or comment below.