The Quarterly Business Review - Maven Sales Challenge

Challenge Objective

For the Maven Sales Challenge, you'll play the role of a BI Developer for MavenTech, a company that specializes in selling computer hardware to large businesses. They've been using a new CRM system to track their sales opportunities but have no visibility of the data outside of the platform.

In an effort to become a data-driven organization, you've been asked to create an interactive dashboard that enables sales managers to track their team's quarterly performance.

Data Set

CRM Sales Opportunities - B2B sales pipeline data from a fictitious company that sells computer hardware.

• File type: CSV
• Data structure: Multiple tables
• # of records: 8800
• # of fields: 18

Challenge Tasks

1. Load the data set into Power BI
2. Create a data model for the data set
3. Identify and create performance measures
4. Create dashboard

Challenge Execution

Load the data set into Power BI

I started with the sales_pipeline (fact) table, securing that close_value field was defined as Fixed Decimal.

Second table imported was the accounts (dimension) table. Here there was an issue with formatting of the revenue field, due to having . instead of , as a decimal divider. This was corrected so the field could be correctly formatted as Fixed Decimal. The values were also multiplied by 1 million, (as the data is in millions of dollars), for easier calculations and summaries later on. The data in the sector field was capitalized for better presentation.

The third table, sales_teams (dimension), had no data issues in it and could be imported unchanged.

The last table to be imported was the products (dimension) table. The only change here was to define the sales_price field as Fixed Decimal. An inconsistency was also found in that "GTX Pro" was spelled "GTXPro" in the sales_pipeline table. This was corrected by updating the values in the sales_pipeline table.

Create a data model for the data set

For the data model an extra dimension table, calendar, was created. This table holds date dimensions like month and quarter for the dates in the data set. The field YearQuarter is formatted like "2017 Quarter 4" in order to make it easier for the user to see which data is being represented in the dashboard.

The final model looks as below (The active relationship to the calendar table is to close_date, while the inactive one is to engage_date.

Identify performance metrics and create measures

Metrics

• Engaged Customers vs. Last Quarter
• Won Opportunities vs. Last Quarter
• Win Ratio vs. Last Quarter
• Revenue vs. Last Quarter
• Revenue by Product (Top 3)
• Revenue by Customer (Top 3)
• Revenue by Country
• Highest Revenue (Sales Agent)
• Most Wins (Sales Agent)
• Highest Win Ratio (Sales Agent)
• Most Units Sold (Sales Agent)

Measures

• Wins = CALCULATE(COUNTA(sales_pipeline[opportunity_id]), sales_pipeline[deal_stage] = "Won")
• Losses = CALCULATE(COUNTA(sales_pipeline[opportunity_id]), sales_pipeline[deal_stage] = "Lost")
• WinRatio = [Wins] / ([Wins] + [Losses])
• UnitsSold = COUNTX(sales_pipeline, sales_pipeline[close_value] / RELATED(products[sales_price]))

Create the dashboard

Final result