PBI Lab - Sales Agent Performance

The Sales Agent Performance report shows the sales team performance from 2017 to 2019 comparing actual vs targeted sales, slicing the sales data by representative, and also shows specifically where the sales are generated.

The Data Story

This Sales Agent Performance Power BI report visualizes the sales team performance from 2017 to 2019 and measures the performance of total reseller sales versus targeted sales over the years.

This analytics solution shows how the sample use-case, Adventure Works Cycle company, expanded into international markets via B2B sales over time: from North America in 2017, Europe in 2018, and into the Asia-Pacific region in 2019.

User Experience

The report consists of 5 report pages: only the main report page (titled Sales Agent Performance) is visible. Interact with the other 4 report pages via tooltips and drillthrough capabilities.

List of report pages:

Sales Agent Performance: main report page of the report (visible)

Sales by Agent Detail: drill through report page activated with a right click from the sales table (hidden)

Sales Agent Ranking: bookmarked report page that is activated by left clicking 3 buttons on the main report page (Orders, Sales and Performance); it is also a drillthrough report page that can be activated with a right click of the mouse on the sales table (hidden)

Map Tooltip: activated by hovering the pointer on one of the bubbles in the Map visual (hidden)

Sales Agent Tooltip: activated when hovering the pointer on the agent name in the Card visual (hidden)

Dynamically activate an agent business card by selecting a sales table row: a business card for the agent selected will appear, and the ranking scorecards, agent business card and the map will dynamically appear/change.

Hover the pointer over the agent name within the agent business card (top left) and a tooltip page will appear showing additional information for the specific agent.

From the sales table (bottom left) the user can right click on top of a row and 2 drill through options will appear: drill to the Sales by Agent Details report page or drill to the Sales Agent Rankings report page.

Hover the pointer over a location point on the sales map (bottom right) and a tooltip will display sales to the specific location.

Showcase expansion of sales by using the:

Clustered Column Chart on the top right. By clicking each year on the bar chart dynamically changes the map and shows global expansion via a visual trend analysis North America, then Europe and finally, the Pacific region.

Filter Pane (top right corner of the report page): initially, the filter pane is a small button. Click to expand, and a small button and 2 Slicers will appear: Year and Quarter, and is developed with a combination of Bookmarks functionalities.

Tooltips Pages: provide details and display additional information when a user hovers with the pointer over a specific data category

Data Model

The data model is built using an extended version of the Microsoft SQL Server AdventureWorks database (a fictitious, multi-national manufacturing company) to showcase SQL Server 2016 features.

The data model built within Power BI consists of 8 tables and their relationships.

Dimension Tables

There are 6 Dimension tables:

DimDate: the time dimension that allows slicing of the fact tables by year and quarter, pre-filtered at the data source connection level

DimAgent: the employee dimension slices the fact tables by agent, and retrieves the Sales Group region by agent from the dimension DimSalesTerritory to

DimSalesTerritory: connected to the dimension DimAgent to retrieve the Sales Group region by agent

DimProduct: the product dimension retrieves the product name via an active relationship with the FactResellerSales fact table.

DimResellers: retrieves the reseller name through an active relationship with the FactResellerSales fact table, and location information of the reseller from the dimension DimGeography to plot points on the map. A bidirectional relationship is required between 3 dimensions DimGeography, DimResellers and FactResellerSales

DimGeography: the geography dimension retrieves the reseller location

Fact Tables

There are 2 Fact tables:

FactResellerSales: retrieves the agent sales by - dimensions date, agent, product and reseller. It is the main fact table of the data model.

FactSalesQuota: the sales quota (sales target) fact of the data model, used to retrieve the agent sales quota (target) by quarter