CRM Dashboard
In this section, you'll learn how to turn raw data into an interactive Power BI report. We'll cover everything you need to know to create a complete project from start to finish.
Connect Power BI to your data source, download this Excel CRM Dataset
Understand Tables of your data set, we have Ten tables in data set
CRM Dashboard
A CRM Dashboard is essential for businesses to effectively track, analyze, and manage customer interactions and relationships. It provides a comprehensive, real-time overview of key metrics such as sales performance, lead conversion rates, customer satisfaction, and opportunities, all in one place. By visualizing this data, a CRM dashboard helps sales and marketing teams make informed decisions, improve customer engagement, and streamline workflows. It also enables businesses to monitor the health of their pipeline, track progress toward goals, and identify potential issues before they become critical, ultimately leading to better customer retention and revenue growth. Try it yourself first! Build the dashboard and, if you get stuck, refer to the video for help.
Data Tables
The CRM Dashboard is based on the following data tables:
Accounts
The Accounts table holds information related to customers, companies, or organizations with whom your business interacts. It includes details like account name, type, and status, which are essential for tracking account relationships and managing billing.
Key Columns: Account ID, Account Name, Industry, City, Country
Contacts
The Contacts table holds the details of individuals within accounts or leads. It includes contact details like phone number, email, and address, and links these to related accounts and leads for easy communication tracking.
Key Columns: Contact ID, Name, Phone, Email, Address
Leads
The Leads table stores information about prospective clients who have shown interest in your product or service. It is used to track the progress from initial contact to potential opportunity.
Key Columns: Lead ID, Account ID, Contact ID, Opportunity ID, Quote ID, First Name, Last Name, Lead Status, Lead Source
Opportunity
The Opportunity table tracks potential sales or business opportunities with clients. It includes details about the account, lead, and stage of the opportunity. This table helps in managing and forecasting potential revenue.
Key Columns: Opportunity ID, Lead ID, Account ID, Contact ID, Quote ID, Order ID, Total Amount, Sales Stage, Opportunity Category, Sales Person
Products
The Products table catalogs the products or services your business offers. Each product has a unique ID, name, price, and status, allowing easy tracking of inventory or offerings.
Key Columns: Product ID, Product Name, Product Number, Unit Group
Orders
The Orders table records completed purchases or commitments from clients, detailing the products, quantities, and pricing. It serves as a key table for order fulfillment and billing.
Key Columns: Order ID, Opportunity ID, Product ID, Payment Term, Amount
Quotes
The Quotes table contains price estimates provided to potential customers. It includes information about the opportunity and pricing details, helping sales teams to track and manage the proposal process.
Key Columns: Quote ID, Opportunity ID, Total Quantity, Total Tax Amount
Opportunity Products
This table links products to specific opportunities, tracking which products are part of an ongoing sales opportunity. It enables a detailed view of what products are being considered or sold for each opportunity.
Key Columns: Product ID, Opportunity ID, Opportunity Product ID, Cost Price, Price Per Unit, Selling Quantity, Tax Amount, Total Amount
Quotes Products
The Quotes Products table connects products to specific quotes, detailing which products were included in a given quote. This table helps sales teams keep track of what was proposed in the quoting phase.
Key Columns: Product ID, Quote ID, Quote Product ID, Cost Price, Price Per Unit, Selling Quantity, Tax Amount, Total Amount
Orders Products
The Orders Products table tracks which products were included in an order, with details like quantity and pricing. It plays a crucial role in managing order fulfillment and inventory.
Key Columns: Product ID, Order ID, Order Product ID, Cost Price, Price Per Unit, Selling Quantity, Tax Amount, Total Amount
Data Tables
The following relationships define how the tables are connected:
Leads to Opportunity:
• Join Type: Many-to-One
• Key: Lead ID
• Description: Multiple leads can be converted into a single opportunity, where each lead represents a potential customer. The opportunity is created
when a lead is considered ready for conversion.
Opportunity to Quote:
• Join Type: Many-to-One
• Key: Quote ID
• Description: Multiple quotes can be created for a single opportunity, each representing a different pricing proposal or offer made to the customer.
Opportunity to Orders:
• Join Type: Many-to-One
• Key: Order ID
• Description: Multiple orders can stem from a single opportunity. Once an opportunity is converted into a sale, an order is created to represent the
finalized deal.
Accounts to Leads:
• Join Type: Many-to-One
• Key: Account ID
• Description: An account can have multiple leads, as it can represent a customer or business with various points of engagement or interest in different
products or services.
Leads to Contacts:
• Join Type: Many-to-One
• Key: Contact ID
• Description: Multiple leads can be associated with a single contact. This represents the fact that a contact (individual person) can be related to many
leads within an organization.
Orders to Products:
• Join Type: Many-to-One
• Key: Product ID
• Description: Multiple products can be included in an order. Each product may be part of many different orders, but in the context of an order, each
product is associated with one specific order.
Products to Opportunity Products:
• Join Type: Many-to-One
• Key: Product ID
• Description: Multiple opportunities can include the same product. This means that a product can be part of various opportunities where it is being
considered for sale.
Products to Quotes Products:
• Join Type: Many-to-One
• Key: Product ID
• Description: A product can appear in multiple quotes, each representing a potential deal for the same product. Multiple quotes can be made for a
product, offering different pricing and conditions.
Products to Orders Products:
• Join Type: Many-to-One
• Key: Product ID
• Description: Multiple orders can include the same product, but each product within the order is tied to a specific order. This allows for tracking how
many times a product has been ordered.
Visualization
Design visualizations to present key insights using charts, tables, and maps. The dashboard layout should be structured to display critical data clearly and effectively, with each visualization highlighting relevant metrics, trends, and patterns to assist in data-driven decision-making. Ensure that the design is clean, intuitive, and tailored to the needs of the users, enabling them to quickly analyze and interpret the information.
• Conversion Rate:
The Conversion Rate in CRM (Customer Relationship Management) refers to the percentage of leads or opportunities that successfully move to the next stage in the sales funnel, ultimately becoming customers or deals. It is a key metric for assessing the effectiveness of the sales process and the quality of leads.
Formula:
Conversion Rate =
VAR TotalLeads = COUNT(Leads[Lead ID])
VAR ConvertedLeads =
CALCULATE(
COUNT(Leads[Lead ID]),
Leads[Lead Status] IN { “Closed – Won”}
)
RETURN
IF(TotalLeads = 0, 0, (ConvertedLeads / TotalLeads) * 100 / 100)
• Lost Rate:
The Lost Rate in CRM represents the percentage of leads or opportunities that were not successfully converted and are marked as Closed – Lost or similar status. It helps businesses identify areas of improvement in the sales process.
Formula:
Lost Rate =
VAR TotalLeads = COUNT(Leads[Lead ID])
VAR LostLeads =
CALCULATE(
COUNT(Leads[Lead ID]),
Leads[Lead Status] = “Closed – Lost”
)
RETURN
IF(TotalLeads = 0, 0, (LostLeads / TotalLeads) * 100 / 100)
• Actual Closed Deal Value:
The Actual Closed Deal Value in CRM refers to the total revenue or monetary value generated from deals that have been successfully closed as “Won.” It represents the realized income from opportunities that have been converted into paying customers.
Formula:
Actual Closed Deal Value =
SUMX(
FILTER(
Leads,
Leads[Lead Status] IN {“Closed – Won”, “Closed – Lost”}
),
Opportunity[Total Deal Value]
)
• Expected Closed Deal Value:
The Expected Closed Deal Value is a forecasted revenue metric that estimates the value of deals likely to close in the future. It is calculated by multiplying the deal value by the probability of the deal closing based on the Sales Stage or other metrics.
Formula:
Expected Deal Value =
Opportunity[Total Amount] * Opportunity[Probability]
Expected Closed Deal Value =
IF(
Opportunity[Sales Stage] IN {“Closed – Won”, “Converted”},
Opportunity[Expected Deal Value],
)
Probability =
SWITCH(
TRUE(),
Opportunity[Sales Stage] = “Qualification”, 0.1,
Opportunity[Sales Stage] = “Negotiation”, 0.5,
Opportunity[Sales Stage] = “Closed – Won”, 2,
Opportunity[Sales Stage] = “Closed – Lost”, 0,
0 — Default if no match found
)
• Potential Deal Value:
The Potential Deal Value in CRM refers to the total monetary value of an opportunity or deal at its full potential, assuming the deal is successfully closed without any changes to its scope, pricing, or terms. This value represents the maximum revenue that could be generated from a lead or opportunity, based on the initial expectations set by the sales team.
Formula:
Potential Deal Value =
SUMX(
FILTER(
Leads,
Leads[Lead Status] IN {“Open”, “Qualified”}
),
Opportunity[Total Deal Value]
)
• Average Weeks to Close:
Average Weeks to Close is a CRM metric that measures the average time (in weeks) it takes for deals or opportunities to move from the initial stage (e.g., qualification) to the final stage (e.g., Closed – Won or Closed – Lost). It provides insights into the efficiency and speed of the sales process.
Formula:
Modified Est. Delivery Date =
VAR DeliveryDate = DATEVALUE(‘Opportunity'[Est. Delivery Date])
RETURN
IF(
NOT(ISBLANK(DeliveryDate)),
FORMAT(DeliveryDate + IF(RAND() < 0.5, 1, 2), “DD-MM-YYYY”),
BLANK()
)
Modified Est. Close Date =
VAR DeliveryDateText = ‘Opportunity'[Modified Est. Delivery Date]
VAR DeliveryDate =
IF (
NOT ISBLANK(DeliveryDateText) && LEN(DeliveryDateText) = 10,
DATE(
MID(DeliveryDateText, 7, 4),
MID(DeliveryDateText, 4, 2),
MID(DeliveryDateText, 1, 2)
),
BLANK()
)
VAR ModifiedDate =
IF (
NOT ISBLANK(DeliveryDate),
DeliveryDate – 2,
BLANK()
)
RETURN
IF (
NOT(ISBLANK(ModifiedDate)),
FORMAT(ModifiedDate, “DD-MM-YYYY”),
BLANK()
)
Confirm Close Date =
VAR DeliveryDateText = ‘Opportunity'[Modified Est. Delivery Date]
VAR DeliveryDate =
IF (
NOT ISBLANK(DeliveryDateText) && LEN(DeliveryDateText) = 10,
DATE(
MID(DeliveryDateText, 7, 4),
MID(DeliveryDateText, 4, 2),
MID(DeliveryDateText, 1, 2)
),
BLANK()
)
VAR ModifiedDate =
IF (
NOT ISBLANK(DeliveryDate),
DeliveryDate – 1,
BLANK()
)
RETURN
IF (
NOT(ISBLANK(ModifiedDate)),
FORMAT(ModifiedDate, “DD-MM-YYYY”),
BLANK()
)
Average Confirm Close Weeks =
VAR ConfirmCloseDates =
ADDCOLUMNS(
‘Opportunity’,
“DateDiffDays”,
IF(
NOT ISBLANK(‘Opportunity'[Modified Est. Delivery Date]) &&
NOT ISBLANK(‘Opportunity'[Confirm Close Date]) &&
LEN(‘Opportunity'[Modified Est. Delivery Date]) = 10 &&
LEN(‘Opportunity'[Confirm Close Date]) = 10,
ABS(
DATEDIFF(
DATE(
MID(‘Opportunity'[Modified Est. Delivery Date], 7, 4),
MID(‘Opportunity'[Modified Est. Delivery Date], 4, 2),
MID(‘Opportunity'[Modified Est. Delivery Date], 1, 2)
),
DATE(
MID(‘Opportunity'[Confirm Close Date], 7, 4),
MID(‘Opportunity'[Confirm Close Date], 4, 2),
MID(‘Opportunity'[Confirm Close Date], 1, 2)
),
DAY
)
),
BLANK()
)
)
RETURN
AVERAGEX(
ConfirmCloseDates,
IF(ISBLANK([DateDiffDays]), BLANK(), [DateDiffDays] / 7)
)
