Competitor Sales Analysis with Power BI

In this Power BI case study, I carry out a sales and market share analysis, focusing not only on the company's performance internally, reflecting on how well the products sell, but also externally analyzing how well they are doing against competing manufacturers.

Through Power BI and Power Query Editor, I worked on the development of a complete Power BI report starting from the data integration from multiple sources, data transformation, modeling, and then visualization.

Case study scenario

The goal of this case study is to build a report using a fictional Sales and Market share dataset for a manufacturing company called Sintec. Sintec is looking for a solution:

• To focus on the company's performance internally on how well their products sell

• To analyze other top competitors' sales and how well they are performing against the other competitors' products.

Big Questions: Insights to uncover

A. Who are the top competitors by revenue?

B. What are the best performing segments and products?

C. What does our growth look like over time?

D. How are we performing compared to the previous year's performance?

---

PartⅠ. Discovering Sales Trends

A. Top N analysis: Who are the top competitors by revenue?

First, we want to research the major competitors and gain insights into their sales trends across all regions. In particular, we want to see the top manufacturers and their revenue share across the regions.

According to the treemap, Artisan is the manufacturer with the highest revenue across multiple geographies. With the left stacked chart, we can see the revenue size of the top players by country. Here, we can narrow down the region and ask more detail questions like "What is the total Revenue of Artisans for the Germany region?". It shows that Artisans is generating over 50% of the market share compared to the other 4 competitors in Germany.

Behind the scene: Steps taken

# I created the stacked column chart "Revenue by Manufacturer", showing Revenue by Country with Manufacturer as the Legend.  
# I filtered the visual to display the top 5 manufacturers by Revenue. 
# I enabled data labels and added a zoom slider to help my audience easily read the data. 
# On the right side, I added Top 5 manufacturers by Revenue in a treemap.

B. What are the best and the worst performing segments?

We continue to dive deeper into our analysis and further explore our data to see if we find any historical trends in the data.

Which segment of Sintec has generated the least revenue in USA from 2019 to 2021 under the Urban category? Our report shows that Productivity segment has been generating the least revenue in the Urban category.

Behind the scene: Steps taken

# I created a slicer that enables us to filter by Manufacturer in a drop-down format and filter for Sintec. 
# I created another slicer to filter on Date.
# I analyzed "Revenue" further wiht more dimensions, and created a hierarchy of Category, Segment, and Product in the Products table.

C. % Growth calculation: Current year vs. Last year

We want to track and show the sales growth, and the rate at which a product can increase the revenue from sales during a fixed period. With DAX Calculations, I added the '% Growth' measure in the report and examined the trend and revenue growth compared to last year.

What is the % Growth for the Sintec UE-05 product under the Extreme category for the year 2021? The table shows that the revenue of the Sintec UE-05 increased by 65.94% compared to 20202.

Behind the scene: Steps taken

# Create a new measure PY Sales that calculates Sales[Revenue] for the previous year by using SAMEPERIODLASTYEAR() function:
# PY Sales = CALCULATE(SUM(SALES[REVENUE]),             
                       SAMEPERIODLASTYEAR('DATE'[DATE]))
# Create a measure % Growth that calculates the difference between Sales[Revenue] and PY Sales:
# % Growth = DIVIDE(SUM(Sales[Revenue]-[PY Sales], [PY Sales])

D. Market share analysis

When the data is expressed in a tabular format, it is difficult to understand if the values are higher or lower at first glance. Thus, we apply conditional formatting in the matrix visual to make it more readable. Also, we check how much market share Sintec is holding compared to other manufacturers.

While the first screenshot shows the Sintec Market Share from 2017 to 2021, the second one displays its market share in 2021. Now with the three different colors (red, yellow, blue) in the table, we can distinguish the statue of the revenue growth more intuitively.

Behind the scene: Steps taken

# Apply rule-based conditional formatting on the background for % Growth field of the matrix.
# Red when % Growth ranges from 0 to 40%, Yellow when 41% to 60%, Light Blue when 61% and higher
# Compute Sintec Revenue. The manufacture ID  of Sintect is 4. 
# Sintec Revenue = Calculate(SUM(SALES[REVENUE], 
                             Manufacturer[ManufacturerID] = 4 )
# To calculate market share Sintec Market Share, divide Sintec Market Share by all total revenue 
# Sintec Market Share = DIVIDE(SALES[REVENUE], SUM(SALES[REVENUE]), 0)

PartⅡ. Summary of insights

While doing all the DAX calculations and data exploration in the last part, we could answer big questions and discover following insights.

• Sintec has a total market share of 38.22% in the USA, and is a leader compared to the other manufacturers.

• Quarter 1 in 202 has seen the highest percentage growth of 18.8% compared to last year.

• Sintec's primary competitor is Artisans, generating over 50% of the market share compared to the other four manufacturers in Germany.

• But one good point is that Sintec has 21.15% of the total market share globally and has seen good growth in its revenue compared to the other manufacturers.

All these insights would help the executives in the strategic decision-making process and would certainly help take action from this data.

Now, it is time to streamline and combine these insights and visuals into a story for a great end-user experience. In the next part, I work on improving UI and design and finalize my report.

Part Ⅲ. Data driven story with AI based visuals

In this last part, I use some of the AI-powered visuals and capabilities which are available for the users off the shelf.

• First, I add a Decomposition tree visual which enables the users to drill down into their data to conduct root cause analysis and gain more insights.

• Second, I add add a Key influencers visual, which helps us identify which factors influence certain metrics.

• Finally, I add a Smart narrative visual, which quickly summarize visuals and reports.

The end users can use summaries to understand the data, get to key points faster, and explain the data to others.

By one click on a logo on the top line, we can get advanced insights specifically for the selected company. The above screenshots show the exclusive sales analysis of Sintec.

We can answer interesting questions such as

• Which category is the most influential when it comes to increasing revenue?

• Which product is influencing the revenue to increase the most for Sintec?

---

Thank you for reading this case study with Power BI. In this post, I produced the end-to-end report for end users and discovered business insights along the way. I designed and developed an interactive report that Sintec's executives can use to do the sales analysis compared to their top competitors in the market and take the right actions out of their data.