Project Description:

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.

Data Model:

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.

Power Query:

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])

DAX:

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] )
)

Dashboard Pages:

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.

Hope you Like it and give me your feedback👇