🍬 Candy Sales Dataset – 10 Advanced DAX Questions and Solution Power BI

Categories: DAX

Tags:

🍬 Candy Sales Dataset – 10 Advanced DAX Questions and Solution Power BI

Candy Sales Dataset – Date Table (MANDATORY STEP)

🔹 Step 1: Create a Date Table (DAX)

DateTable =
ADDCOLUMNS(
    CALENDAR(
        MIN('CandySales'[Date]),
        MAX('CandySales'[Date])
    ),
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMM"),
    "Quarter", "Q" & FORMAT([Date], "Q"),
    "YearMonth", FORMAT([Date], "YYYY-MM")
)

This table generates a continuous calendar from the earliest to the latest sales date and adds common time attributes needed for analysis.

🔹 Step 2: Mark as Date Table (CRITICAL)

In Power BI:

Go to Model view
Select DateTable
Click Mark as Date Table
Choose Date

Step 3: Create Relationship (MANDATORY)

Go to Model View
Create a relationship:
- DateTable[Date] → CandySales[Date]
Cardinality: One-to-Many
Direction: Single
DateTable is the date dimension

This step enables all time-intelligence DAX to work correctly.

🔹 Step 4: Base Measures (Foundation Only)

These are NOT KPIs, only required building blocks.

Total Sales =
SUM('CandySales'[Sales])

This measure provides a reusable base for all advanced calculations.

Total Profit =
SUM('CandySales'[Profit])

This measure allows margin and trend calculations without repeating logic.

1️⃣ Question: Which candy categories are growing or declining year over year?

DAX Measure – YoY Sales Growth %

// Without Date Table use this 
YoY Sales Growth % =
VAR CurrentYearSales =
    CALCULATE(
        [Total Sales],
        YEAR('CandySales'[Date]) = YEAR(MAX('CandySales'[Date]))
    )
VAR PreviousYearSales =
    CALCULATE(
        [Total Sales],
        YEAR('CandySales'[Date]) = YEAR(MAX('CandySales'[Date])) - 1
    )
RETURN
DIVIDE(CurrentYearSales - PreviousYearSales, PreviousYearSales)

// With Date Table use this 
YoY Sales Growth % =
VAR CurrentSales =
    [Total Sales]
VAR LastYearSales =
    CALCULATE(
        [Total Sales],
        SAMEPERIODLASTYEAR(DateTable[Date])
    )
RETURN
DIVIDE(CurrentSales - LastYearSales, LastYearSales)

Explanation:
This measure compares current year sales with the previous year and shows whether a category is growing or declining in percentage terms.

2️⃣ Question: Which products sell a lot but generate poor profitability?

DAX Measure – Profit Margin %

Profit Margin % =
DIVIDE(
    SUM('CandySales'[Profit]),
    SUM('CandySales'[Sales])
)

Explanation:
This measure shows how much profit is generated for every unit of sales, helping identify high-volume but low-margin products.

3️⃣ Question: Which regions have strong sales but weak margins?

DAX Measure – Margin vs Sales Index

Margin vs Sales Index =
[Profit Margin %] - 
CALCULATE(
    [Profit Margin %],
    ALL('CandySales'[Factory])
)

/*
This chart compares each factory’s profit margin against the company’s average margin.
A positive value means the factory is more profitable relative to its sales, while a negative value means it is underperforming.
Sugar Shack stands out as the most efficient factory, while Let’s O’ Nuts shows margin erosion despite sales, making it a key area for corrective action.

🧠 Why this is a great advanced visual

Not raw profit
Not raw sales
Relative performance → decision-making metric

Perfect for:

Executive dashboards
Factory benchmarking
Cost optimization discussions
*/

Explanation:
This measure compares a region’s margin against the overall average margin to identify underperforming regions.

4️⃣ Question: How seasonal are candy sales across months?

DAX Measure – Monthly Revenue Contribution %

// Without DateTable
Monthly Revenue Contribution % =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALL('CandySales'[Month]))
)

// With DateTable
Monthly Revenue Contribution % = 
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        ALL(DateTable[Month Number], DateTable[Month Name])
    )
)

Explanation:
This measure shows how much each month contributes to total annual revenue, highlighting seasonal spikes.

5️⃣ Question: Which products are improving margins even if sales are flat?

DAX Measure – Margin Trend

Margin Trend =
[Profit Margin %] -
CALCULATE(
    [Profit Margin %],
    DATEADD('CandySales'[Date], -1, YEAR)
)
// with date table

Margin Trend =
VAR CurrentMargin =
    [Profit Margin %]
VAR LastYearMargin =
    CALCULATE(
        [Profit Margin %],
        SAMEPERIODLASTYEAR(DateTable[Date])
    )
RETURN
CurrentMargin - LastYearMargin


/* 🧠 What this measure really does
CurrentMargin
→ Margin for current filter context (year, month, product, factory, etc.)

LastYearMargin
→ Margin for the same period last year

Result
→ Positive → margin improved
→ Negative → margin declined

This measure compares the current profit margin with the same period last year.
It uses the Date table to shift the time context correctly, making the trend dynamic and reliable.*/

Explanation:
This measure compares current margin with last year’s margin to detect improvement or decline.

6️⃣ Question: What percentage of revenue comes from the top 20% of products?

DAX Measure – Revenue Contribution %

Revenue Contribution % =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], ALL('CandySales'[Product]))
)

Explanation:
This measure shows how much each product contributes to total revenue and is used for Pareto analysis.

7️⃣ Question: Which categories depend heavily on discounts to sell?

DAX Measure – Discount Dependency Ratio

Discount Dependency Ratio =
DIVIDE(
    SUM('CandySales'[DiscountAmount]),
    SUM('CandySales'[Sales])
)

Explanation:
This measure indicates how much revenue depends on discounts, signaling unhealthy sales dependency.

8️⃣ Question: How is customer buying value changing over time?

DAX Measure – Average Order Value Trend

AOV Trend =
DIVIDE(
    SUM('CandySales'[Sales]),
    DISTINCTCOUNT('CandySales'[OrderID])
)

Explanation:
This measure calculates the average value per order and helps track customer spending behavior.

9️⃣ Question: Which regions show unstable sales performance?

DAX Measure – Sales Volatility

Sales Volatility =
STDEVX.P(
    VALUES('CandySales'[Month]),
    [Total Sales]
)

Explanation:
This measure captures fluctuations in monthly sales to identify unstable or risky regions.

This measure calculates how much sales fluctuate over time, which helps assess the stability or riskiness of revenue. It evaluates total sales for each distinct month and then applies a population standard deviation across those monthly values. A higher Sales Volatility value indicates large swings in sales from month to month, suggesting unstable or seasonal demand. A lower value means sales are more consistent and predictable over time. This metric is especially useful for comparing regions, factories, or product categories to identify areas with unpredictable performance. High volatility may require better demand forecasting, inventory planning, or promotional control, while low volatility generally reflects steady customer demand and operational stability.

🔟 Question: Which products should be discontinued?

DAX Measure – Discontinuation Score

Discontinuation Score =
IF(
    [YoY Sales Growth %] < 0 &&
    [Profit Margin %] < 0.1,
    1,
    0
)

Explanation:
This measure flags products that are declining in sales and generating weak margins.