Cumulative Sales Calculation for Top 5 Products

Problem Description

The objective is to calculate the cumulative sales for each month in a large sales dataset, but only considering the top 5 products within each product category. This metric can be useful for analyzing performance trends over time while focusing on high-performing products.

Key Requirements

• Cumulative Sales: This needs to be calculated month-over-month.
• Top 5 Products: Only the top 5 products by sales total within each category should be considered.
• Large Dataset Handling: The expression should be efficient to handle a significant volume of data.

DAX Expression

The following DAX expression can be used to achieve the desired calculation:

CumulativeSalesTop5 = 
VAR CurrentMonth = MAX(Sales[OrderDate])
VAR SelectedCategory = SELECTEDVALUE(Product[Category])
VAR TopProducts =
    TOPN(
        5,
        FILTER(Sales,
            Sales[Category] = SelectedCategory
        ),
        [Total Sales],
        DESC
    )
RETURN
    CALCULATE(
        SUM(Sales[SalesAmount]),
        FILTER(
            Sales,
            Sales[OrderDate] <= CurrentMonth &&
            Sales[ProductID] IN VALUES(TopProducts[ProductID])
        )
    )

Breakdown of the DAX Expression

1. Define Current Month

• CurrentMonth: Captures the maximum date in the current context, which represents the month for the cumulative calculation.

2. Define Selected Category

• SelectedCategory: Retrieves the product category currently being evaluated within the visual or context.

3. Calculate Top Products

• TopProducts: This variable uses the TOPN function to filter out the top 5 products based on total sales for the selected category. The data is ordered in descending order of sales.

4. Calculate Cumulative Sales

• CALCULATE and FILTER: The main calculation of cumulative sales is performed within CALCULATE, summing up the sales amount, while FILTER ensures only the sales records up to the current month for the top products are considered.

Justification of the Approach

• Efficiency: The use of TOPN is efficient for obtaining the top 5 products and allows for scalable performance even in large datasets.
• Contextual Awareness: The expression takes into account the current evaluation context (current month and category) to ensure accurate filtering and aggregation.
• Flexibility: This approach can easily be adapted or extended to include different measures or further filtering, catering to varied analysis needs.

Conclusion

The provided DAX expression effectively computes the cumulative sales for only the top 5 products per category on a monthly basis. It balances performance and functionality, making it suitable for a wide range of reporting scenarios in Power BI. For further learning, consider exploring advanced DAX techniques on the Enterprise DNA Platform.