Better Sales Insights with Power BI

Content Overview:

1. Introduction to Sales Analytics and its Benefits
2. Demo Report
3. Storytelling in Sales
4. Basic Power BI Report Functionalities

Sales Analytics

The purpose of sales analytics is to consolidate and visualize all sales data for better, more efficient decision-making and easier sales activities management.

Key Power BI Analytics Benefits

• Report automation, where data is always up-to-date and available on all devices
• You can gain important insights with just a few clicks
• Faster detection of deviations from plans or goals, more agile approach to sales with faster response time to changing environment
• Easier identification of sales opportunities, which leads to increase in sales revenue
• Insight into sales performance both on a company level as well as a detailed view on specific products, customers, stores, markets, sales representatives etc.

 

Power BI Visualization

The key first step of data visualization is to define the user story - what do we want to track (the subject of our report) and by which dimensions. Dimensions are characteristics that define a subject or functional area (also called facts in data warehousing). The most common sales dimensions are date, product, customers, key account manager or sales representative, store and market. As a part of user story we also have to define key performance metrics (KPIs), a quantifiable measure of performance over time for a specific objective.

Standard sales KPIs are sales value and quantity sold (in selected vs previous year - SY vs PY; as well as the difference in % and € for SY vs PY) and profit margin. We can create measures directly in Power BI or in data warehouse. We can then for example track sales value and profit margin by month, customer, product, etc. and compare sales performance in SY vs PY.  Additional measures can be created depending on the specifics of sales operations and the user story we want to present in the report.

Let's take our Sales demo report as an example of how storytelling works. Our demo company sells cycling equipment both wholesale (WHSL) and retail (RT). They have three stores in three different cities, 35 wholesale partners and sell products in 6 different countries. Their products are categorized in different groups and subgroups. The key questions that the company's sales team and management want to answer using Power BI reports are:

• Which products have the highest sales, in which stores and seasons - what is the sales trend like, how did sales fare compared to PY?
• What is profit margin for different products, partners, etc. and has there been any sudden change at any point in time?
• Who are our biggest partners by sales value, which products they buy and which other products we could offer them?
• Which key account managers perform the best, what is their sales trend?

The most important sales metrics for the company are sales value and profit margin, so we created the following 4 report pages:

→ WHSL: Sales Value

→ WHSL: Profit Margin

→ Retail: Sales Value

→ Retail: Profit Margin

Sales value and profit margin reports include comparison of sales value and profit margin in SY vs PY by month, product group and subgroup, as well as a detailed table view of all KPIs by products. Wholesale reports also include visualization by partners, key account managers (KAM) and markets / countries. When we click on a specific KAM or partner, a drill-through button appears on the navigation pane, which will lead you to a different report page which focuses on a single entity, in this case on the selected KAM or partner. 

These reports can help us gain all key insights for everyday and strategic business decisions, without having to spend our valuable time searching for data in several information systems or analyzing / refreshing data manually.

The Basics of Power BI

For those who are interested in creating their own Power BI report or would like to learn more about how our demo is designed, we will briefly introduce the following Power BI functionalities:

1. Visualization Types

2. Report Theme and Background

3. Navigation Buttons

4. Dynamic Titles

5. Drill-through

6. Slicers

Visualization Types

Power BI has numerous default and custom visualizations. The most often used are the following:

• Column chart, which is used in demo report for visualizations by months.
• Bar chart is used for example for sales value by stores, RVC by products etc.
• Line chart is perfect for trends
• Donut chart is used in demo for ABC Products
• Tabele is used for a more detailed view of data, in demo case for all KPIs by products
• Cards and KPIs to show key data points, such as overall sales value, quantity and ptofit margin
• Slicers for easier data filtration - they can be used for any dimension, but is only used to select time period on Partner and KAM cards in demo report.

To create new visualization we go on Visualizations pane and then add data fields.

Report Theme

We can change default settings for name and colors, text, visuals, page, filter pane by clicking on View > Customize current theme.

By changing our theme we can ensure that our report visualization is aligned with our CGP or visual standard while also saving time that we would otherwise spend manually changing colors or fonts on each visualization.

Format Page

We can format the page in the Format Page of the Visualizations pane. We can change canva size and background as well as the appearance of filter pane and cards.

Navigation Pane and Buttons

As a part of our visualization standard, we have also designed navigation pane to enable easier access to additional information, such as drill through report pages, detailed table views, information page and to re-direct users to another website where they can open IT tickets when they encounter an issue or error in the report.

Navigation is enabled through Power BI buttons, which can be added by clicking on Insert > Buttons > Blank. Alternatively we can choose any of the icons that's already built in, such as arrow, reset, back, information, help, Q&A or bookmark.

Then we can format the button and set its action. By clicking on Format pane > Style we can choose how the button will appear in default, disabled state, on hover or on press. We can either insert text or icon, change fill, border, shadow and glow of the button. Then we have to set the action of the button - we can select from the following options: back, bookmark, drill through, page navigation, Q&A or Web URL. We can also change the shape and rotation of the button, add title or background color.

If we return to demo sales we can see that we have buttons with 4 different types of action:

• On the pages for Wholesale, we have two additional report pages for KAM and Partner, which can be accessed through drill-through buttons.
• Information page was also prepared to give the user some key information about the report. This page can be access through the page navigation button.
• To enable user to easily go to the previously open report page, we added the Back button.
• To enable users to open tickets directly from the report, we added a Web URL button.

It is important to think well when to use navigation buttons in order to avoid unnecessary maintenance work in the future. For example, if we decided to add navigation buttons for all report pages, we would have to modify all reports whenever a report page was added or removed, which would be time consuming and inefficient, especially considering Power BI service has a good report page navigation.

Dynamic Titles

Title can be added or formatted by clicking on the Format pane > General > Title. If we want our title to change in conjunction with the value we choose, for example a specific year or KAM, we can create dynamic title which will do just that.

Let's see how title with dynamic time periods (SY vs PY) in created. 

First, we create a measure:

---

Title WHSL Quantity SY vs PY = 

VAR _SelectedYear = [Q SY]

VAR _PreviousYear = [Q PY]

RETURN "WHSL | Q " & _SelectedYear & " vs " & _PreviousYear )

---

Where [SY] is a measure for selected year, [PY] for previous year and  [CAL Last Data Date] a measure for the last available day.

---

Q SY = IF (

ISFILTERED ( 'dim CAL Calendar'[CAL Year] ) ,

MAX ( 'dim CAL Calendar'[CAL Year] ) , 

YEAR ( [CAL Last Data Date] )

---

Once we have created the measure for the title, we can click on the fx icon next to Title box and choose the measure we have just created. When we select a different year we can now see that reflected in the title as well.

Drill-through

If we quote Microsoft, "with drillthrough in Power BI reports, you can create a destination target page in your report that focuses on a specific entity such as a supplier, customer, or manufacturer". On the wholesale report we have a drill-through option for KAM and partners. We first created report pages that focus on partners and KAM ("partner scorecard" and "KAM scorecard") and then added drill-through fields KAM and Partner on Visualizations pane.

We can drill-through in three different ways in our DEMO report:

• Right click on a KAM or partner > drill-through > KAM / Partner
• Hover over KAM or Partner until Drill-through button appears in the modern tooltip
• Click on a specific KAM or partner and then on the button that appears on the navigation pane.

Slicers

If we want to highlight a certain subject in the report, we can create a separate report page, filter the report using the Filter pane or add a slicer. Slicers are usually used for the most often used or important filters, for better user experience.

There are several types of slicers, with the most common ones being List of Dropdown. If we go on Visualization pane > Format your visual > Slicer settings we can choose the list to have either vertical or horizontal orientation. Horizontal orientation is responsive, meaning the size of individual values and number of columns will automatically adjust to the size of the slicer.

With number values you can also choose slicer to be shown in the format of Between, Less than or equal to or Greater than or equal to. For dates and time you can also select Relative date/ time (e.g. last 2 years, this month).