Mastering Brand and Product Portfolio in Power BI

Mastering brand and product portfolio analysis is an extremely important skill for industries like fast moving consumer goods, pharmaceuticals, automotive and many others. It's essentially a core analytical skill that can help you use data for benchmarking against competitors, for category and brand management, and for gaining insights for practically every marketing and sales decision and campaign.

Let's begin by examining a sample dashboard we've created. Here's one for a fictional personal care product company displaying various data points regarding market development. It showcases revenue across product categories, sales volumes, average prices, and market share, encompassing benchmarking features.

What is brand and product portfolio analysis?

Brand and product portfolio management is essentially about leveraging your data—like market share, sales volume and growth, brand penetration, customer satisfaction scores, and brand loyalty—to boost performance and glean deeper customer insights. This enables you to enhance competitive positioning, allocate resources more efficiently, seize market opportunities, and pinpoint potential risks.

Brand portfolio analysis offers significant benefits across various industries, notably fast-moving consumer goods, food and beverage, automotive, retail, healthcare, technology, fashion, telecommunications, financial services, and travel and tourism.

This information is invaluable for numerous roles, encompassing brand and product managers, marketing directors, sales managers, and C-level executives.

Getting your data 

When starting out with product portfolio analysis, you first need benchmarking data that is usually purchased from market measurement firms like Nieslen, IRI, Mintel, Statista, Gartner and many others. While some provide more general information, there are also companies that specialize in specific industries, such as pharmaceuticals. 

These companies offer different types of data in terms of subject matter and granularity. Of course, while there are standard KPIs like sales data, market share data, price per unit and weighted distribution, there’s also data covering consumer demographics, promotional data, shelf space data and so on. 

However, all this data must be purchased since it's not readily available. Thus, ensuring it delivers genuine value is crucial, as its effective utilization is paramount.

Traditionally, all this data and analysis was treated as an add-on to “real” analytics, which primarily focused on internal sales figures or financials. However, at Zebra BI we believe that the end results like EBITDA and profit and revenue are actually a consequence of making really good business decisions. And those business decisions need to be taken on a level of a brand, product, or market. 

A detailed look at the sample dashboard

Let's delve into how to practically leverage this data. Our first demonstration involves a portfolio analysis dashboard tailored for fast-moving consumer goods (FMCG). It's built using data akin to what you'd obtain from a provider such as Nielsen.

In our example, we are looking at a market overview. Here, you have revenue and units sold per individual product categories as well as the average price per unit. These categories might be then divided further into subcategories to help you pinpoint your exact product positioning. Using this data, you can calculate your market share in revenue or the number of units sold. 

You can use these reports to gain insight into categories. You can look at it and see whether it’s growing or shrinking, how big a category is compared to others and what is the rate of growth.

Focus on getting time periods right

To get these comparisons right, you need to make sure your time periods are in order. You might be buying your data on a weekly, monthly or bimonthly basis, which depends on the provider and the budget you have. However, your goal is to have repeatable and equidistant periods. This will allow you to compare data for these periods to previous periods of the same length.

To ensure you have consistent data, you need to regularly load everything into Power BI incrementally. This way you will add it to your unified data model in Power BI.

Once you have the data, we recommend that you create a starting page like this that shows you how individual categories are developing as well as your top competitors. Also make sure you highlight your own company and your products or brands so that you can easily understand what is going on in the market and benchmark against your competition.
This dashboard provides quick information about market shares as well as changes. We also added a tooltip area chart that appears once you hover over the value showing changes in market shares:

Once your users hover over this, they will understand not only what has changed, but also how the market share is changing over time. Here, you can easily see why it is so important to buy and handle data over equal periods.

Diving into category analysis

Let’s move onto the portfolio to look at what is happening within individual categories.

Here is the hair care category with the benchmarking analysis of all the companies. In an analysis like this, you will typically have the data on revenue, the sales volume, and market share. You need to do an analysis of individual KPIs versus previous year to see how they are changing. 

Once you have a general idea, you can expand information about your company to look at the drivers of your growth or click on it to see how individual KPIs are changing over time.

You can even do this type of analysis on individual sub-brands as well as product categories. Your users can easily switch between hair care products or family or skin care products to review data for their specific area.

Adding more data

Let’s look at a more detailed portfolio analysis report that contains more data. In addition to revenue, you can also add data on units sold and market share. 

There’s another thing that can greatly enhance your reports and will be particularly well received by senior executives - comments. We strongly recommend that you include commentary in your portfolio analyses to explain movements in market shares and other KPIs.

Zebra BI makes it very easy to add commentary. You simply turn on the annotation layer to add meaningful commentary to individual values making up your reports. These comments will be stored with the presentation and as you add commentary month after month, you will get a central knowledge base that you can use to review your data for previous months and understand clearly what is going on based on your commentary.

Design trick: Change the icon based on selected category

Let’s look at some design tricks used to create these reports. One of the most popular ones is how to change the icon when you switch between categories. Using icons or brand pictures in your reports is a great way to make your reports more easily readable and impactful. 

To achieve this, we used the native Power BI matrix. 

To display individual icons in the matrix, we needed to create a separate table with categories, which essentially contains category names, their IDs to be used for sorting and URLs to the icons we want to use. 

You could also load pictures in PNG or SVG format. 

Once you have this set up, click on the matrix and select the Icon value in the Data pane in Power BI. 

 Then, set the Data category value in the Column Tools tab to Image URL. 

This needs to be done because by default every visual will simply display the text in your field. Now, once you filter your data, the matrix will display the corresponding icon. 

Building a portfolio analysis report from scratch

Now that we’ve handled some easy design tricks, we’ll show you how to use Zebra BI to build a report like this quickly. First step is to add a Zebra BI Tables visual to your report. Afterwards, add data, such as revenue, revenue in the previous year and company. 

Zebra BI Tables already calculated the growth rates, so you already have some additional information. 

The first thing you need to do is to distinguish your company from the others to facilitate benchmarking. Right-click on the row name and select Format from the menu that appears. 

You can format either the text or highlight the entire row containing your company information. 

Your next step should be to add individual brands to your visual so you can better understand how they contribute to your operations. Simply take the Brand value from your data pane and drag it to the Category placeholder below Company value. This will create a hierarchical table where you can expand individual companies to review how their individual brands perform. 

At Zebra BI we’re all about information density, so you can also add additional measures, like market share. Simply drag the MA Share % value to your Values placeholder to add it to your table. Our recommendation is to use the pin chart to visualize the market share data, since you usually use columns or bars to visualize your revenue data. As a rule your goal is to visually separate different types of data. 

And since you’re already working with market share data, you can also add the change of market share. Take the value MA Share Δ and drop it into your Values placeholder. Then, set the Measure type value to Absolute variance to ensure it display correctly. 

You can also set the Chart type to bar chart to visually highlight changes in market share. Click on the drop down arrow in the column heading and set the value.

You can just keep going and add more values. A typical value for a portfolio analysis report is the number of units sold, so you can just add that to your Values placeholder as well. 

Adding comments to you portfolio analysis 

You can now start adding comments using the Annotation layer feature in Zebra BI. Click on the Add annotations icon at the top right of your chart. 

In the small selection panel that opens, select the Add comment button. 

Once you’re in the annotation mode, you can simply select the value that you want to comment and click. Zebra BI will automatically add the comment pane, where you can add your comments and explain the data. 

You could also turn of the comment box and just display comments as a tooltip. In the Visualizations pane, select Format your visual option at the top and then turn off the Comment box setting. 

Now, comments are only visible as tooltips in your visual. 

Compare trends and benchmark against competitors

The next thing your performance analysis needs is an effective comparison of trends. Here is an example we created in Zebra BI.

You can understand a lot by looking at this report. You see that your company is second while all other companies have smaller market shares. You can see that you’re growing while most of your competitors are losing market share. 

This type of chart is easy to recreate. You need to add a Zebra BI Charts visual to your canvas and then display market share values by period and grouped by company. Here is how the data pane looks for this chart: 

This creates a visual with small multiples that you can then further design and format to your liking. In the Format Visual pane, expand the Small multiples group. You can set the layout, where we recommend using the Auto option because it delivers the best results in most situations. 

You can enable the Show grid option to separate individual charts. Also, you can use the Top N option, when you have a large number of values and only want to display the top 10. 

A complex portfolio analysis for the automotive industry

We’re now moving to a more complex example of portfolio analysis. For the automotive industry, we’ve created a report that combines multiple KPIs and dimensions and makes them dynamic, so users can easily select the view they want to see. Using a dropdown menu, any user can change the KPI they want to review and switch the entire page. 

Everything about this is dynamic. As you can see, even the comments change when you switch between KPIs and are only shown when actually relevant to the data you’re looking at.

There are a couple of tricks to making this work. The first is to make sure values are formatted correctly based on the type of data. For example, you need to have your percentages formatted in a different way than millions of dollars. 

You can do this by opening the Format visual pane and expanding the Data label group, where you should set the Units value to Power BI. Now you can specify the dynamic format for each of the KPIs however you like in DAX. 

The second trick is to cross-filter comments to make sure comments only appear when relevant. To achieve this, you need to filter based on the selected KPI, region and year. 

Selecting the right rolling time period

There is another thing you need to ensure when creating reports examining trends. The best practice is to use a 13-month rolling period, which ensures you always start your time series with the same month previous year. This allows you to compare this year’s May data to last year’s May data. Of course, the same principle applies if you have quarterly or bimonthly data. 

This is particularly important with competitive data that is provided by Nielsen and others. As you load new data into your analysis and switch the month, everything works and there is no additional work required to adjust your reports. 

Steps for implementing effective brand and portfolio analysis

This post is a brief overview of how you can implement brand and portfolio analysis using Zebra Bi where the goal is to give you some inspiration as you start your own efforts. 

Here are the key steps: 

• Load your brand data into Power BI
• Identify the data provider relevant to your industry
• Select meaningful KPIs
• Load data in repeatable periods into Power BI incrementally
• Use data visualization to drive decisions

When designing reports for brand and portfolio analysis, focus on small multiples, tables with visual columns and commentary. This will help you get the most out of your data so that people start using your reports for actionable insights that inform the most meaningful decisions and actions in your company. 

Prefer to get this guide in the form of a video tutorial? We got you! Watch the recording on demand free of charge.