SUMX not totaling correctly (Context Transition not applying?)

Hi jwin2424 ,

The issue with totals not aligning arises because DAX evaluates grand totals differently from row-level calculations. While your measure works correctly at the row level, the grand total does not automatically reapply the same logic, leading to discrepancies. To address this, you can create a summarized table grouped by Order No, filter the grouped data to exclude orders below the threshold and blank Order No, and explicitly calculate the grand total using these filtered results. Introducing a variable for the threshold value improves maintainability, allowing changes to be made in one place if the threshold changes in the future.

Here’s the updated measure with the threshold defined as a variable:

OVER 500000 = 
VAR SalesThreshold = 500000
VAR GroupedOrders =
    SUMMARIZE(
        'Revenue Source',
        'Revenue Source'[Order No],
        "TotalSales", CALCULATE(SUM('Revenue Source'[Master Net USD]))
    )
VAR FilteredOrders =
    FILTER(
        GroupedOrders,
        NOT ISBLANK([Order No]) && [TotalSales] > SalesThreshold
    )
RETURN
    SUMX(FilteredOrders, [TotalSales])

In this measure, the SalesThreshold variable sets the threshold for orders, ensuring you only need to update this value if it changes in the future. The SUMMARIZE function groups data by Order No and calculates total sales for each order. The FILTER function then removes rows where the Order No is blank or the total sales are less than the threshold. Finally, SUMX ensures that the correct total is calculated by summing the filtered sales. This approach explicitly calculates the grand total, ensuring consistency between row-level results and the total. By introducing the SalesThreshold variable, the formula is not only accurate but also easier to maintain and update.

Best regards,