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:

1. Why is calculating product profitability important?
2. How do we calculate it?
3. How do we use Microsoft Power BI to generate a product profitability dashboard?
4. How do we analyze the data and interpret the results?
5. Closing Remarks

1. 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?

Figure 1— Formula for Gross Profit

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.

Figure 2— Gross Profit

To calculate gross profit %, we use the formula in Figure 3.

Figure 3— Gross Profit %

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.

Figure 4— Calculating Gross Profit for Washing Machine

To get the gross profit %, we divide the $645 by the selling price of $995 which is 64.82% as shown in Figure 5.

Figure 5— Calculating Gross Profit % for Washing Machine

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.

Figure 6— Flow of discussion

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).

Figure 7— Files in the Google Drive

We are now ready to import the data.

Figure 8 — Import Data

First, we load the data into Power BI by clicking ‘Get data’ and selecting ‘Excel workbook’ as highlighted in Figure 9.

Figure 9— Get data

This will open a pop-up window. Locate where the files are saved, and select ‘2020 Transactions’. Then, click ‘Open’.

Figure 10— Select ‘2020_Transactions’

Now that we have loaded the information we need, we can start transforming the data for our use.

Figure 11 — Transform Data

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.’

Figure 12— Navigator Window

The Power Query Editor will appear. Check how the columns are formatted, and remove any excess columns not needed as highlighted in Figure 13.

Figure 13— Remove excess columns

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.

Figure 14— Formatting into Percentage

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]

Figure 15 — Add Custom Column

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.

Figure 16— Calculating Total_Sales

Click on the Column Tools, and select thousands separator [,] to format the numbers.

Figure 17— Thousands separator

Do the same set of steps to calculate the column values under Total_Cost and Total_Gross_Profit.

Figure 18— Updated ‘2020_Transactions’ table with ‘Total_Sales’ ‘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]

Figure 19 — Gross profit % formula

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.

Figure 20 — Visualize Data

Click the report icon (as seen in the red box from Figure 20) which shows the empty white canvas.

Figure 21— Report icon

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 %.

Figure 22— Card visualization

Then under the ‘Fields’ section, select ‘Total_Sales’ as highlighted in Figure 23.

Figure 23— Selecting Total_Sales

The Total Sales will show in the report as can be seen in Figure 24.

Figure 24— Total_Sales KPI

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.

Figure 25— Selecting Average for Gross Profit %

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.

Figure 26— Stacked Column Chart by product and month

Next, let’s look at the profitability trend per month. This time, let’s select the ‘Line and Stacked Column Chart’.

Figure 27— 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.

Figure 28— Adding the Total Gross Profit for the Line values

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’.

Figure 29— Treemap

The completed dashboard will look as shown under Figure 30.

Figure 30— The completed dashboard

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.

Figure 31— Analyze Data

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%.

Figure 32— Selecting Refrigerator

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.