__STYLES__
Welcome to the Maven Sales Dashboard, where data meets actionable insights. This Power BI report is designed to provide a comprehensive overview of sales performance, through the main sales metrics as below:
šSales Value: SUM of the selling price for "Won" deals.
šWins: Number of "Won" deals.
šConversion Time: the variance in Days between the Engaging Date and Closing Date, for "Won" deals.
šDiscount%: the Average variance in percentage between the RRP (Recommended Retail Price) and the Selling Price.
The Model contains the 4 provided tables for Team, Accounts, Products, and Sales pipeline, and then I added a Date table for better timeline calculations and two extra tables for the RFM analysis.
Most importantly for such a dashboard with calculations and a Date table to be dynamically updated upon updating the dataset, for example, so I made the Date table to dynamically determine the start and End date based on the Sales Data.
Start_Date:
= List.Min (Sales[Engage_Date])
End_Date:
= List.Max(Sales[Close_Date])
As the project scope was focused on the Quarter over Quarter performance so I focused my DAX measures on the quarter level, highlighting the Last Quarter (LQ) performance vs the Previous Quarter (PQ)
For example the DAX for LQ Won Deals:
LQ #Won =
VAR vLastQuarter =
CALCULATE (
"Q" & QUARTER ( MAX ( Date_LookUp[Date] ) ) & " "
& YEAR ( MAX ( Date_LookUp[Date] ) ),
ALL ( Date_LookUp )
)
VAR vResult =
CALCULATE (
[#Won],
Date_LookUp[Quarter & Year] = vLastQuarter,
ALL ( Date_LookUp )
)
RETURN
vResult
For the Previous to Last Quarter Won:
Previous to LQ #Won =
VAR vLastQuarterOffset = CALCULATE(
MAX( Date_LookUp[CurrQuarterOffset] ),
ALL( Date_LookUp )
)
VAR vResult = CALCULATE(
[#Won],
Date_LookUp[CurrQuarterOffset] = vLastQuarterOffset -1,
ALL( Date_LookUp )
) Return vResult
And so for the rest of the metrics...
What about dealing with dates, as we have Engaging Date and Closing Date, so I created an Inactive relationship between the Date table and Sales table as shown in the data model, and used DAX to influence certain relationships, as in the below example:
#Won =
CALCULATE (
DISTINCTCOUNT ( Sales[opportunity_id] ),
Sales[deal_stage] = "Won",
USERELATIONSHIP ( Date_LookUp[Date], Sales[Close_Date] )
)
Always thinking in analyzing Sales data as a funnel, firstly understanding the company level and then breakdown by region, Manager, Agent, and the same for Products.
The Main project requirements were for the quarter-over-quarter performance analysis but I couldn't this chance to make further RFM analysis and What IF scenario analysis.
The Dashboard consists of main 4 pages:
šQuarter Over Quarter: Delve into the heartbeat of Maven Sales as we track performance quarter over quarter. This page shows sales metrics for the Last Quarter vs the Previous Quarter.
šSales Team: Get up close and personal with our sales team analysis. Uncover the driving forces behind our success as we dissect metrics by sales managers and agents. From individual performance to team dynamics, this page offers a granular understanding of who, what, and how our sales force is contributing to Maven's bottom line.
šRFM Analysis: Unlock the secrets of customer segmentation with RFM analysis. By analyzing Recency, Frequency, and Monetary scores, we gain invaluable insights into customer behavior and preferences.
šWhat If ?! : Embark on a journey of strategic foresight with What If analysis. Explore hypothetical scenarios and simulate the impact of winning lost deals on our sales figures. With the ability to choose certain Discount% to apply on the RRP, and what Products to apply this discount% to.