running total performance issues

Hi, thanks for your replies 😊

Anonymous your approach runs into the same issues, since [running total sales] uses iterators as well and eats up way too much memory.

I have come up with a different solution where I can avoid running totals alltogether.

The approach is based on z-scores (standard deviation form arithmetic mean). That is something, the storage engine can calculate incredibly fast for huge datasets.
The second step is, to find the correct threshold for the cumulative sales percentage (e.g. all customers with z-score > 2.5 are responsible for 50% of all sales). This second step of parameter calibration is also very simple in terms of computation power.
I've come up with a consistent ABC formula which works fast for millions of customers.

ABC-Class =
-- Performs a dynamic ABC classification
-- Replaces traditional running-total iterators with Z-scores
-- → scales to very large datasets without the usual iterator slowdown
-- Target cumulative percentages are approximated within a few points

-- “Magic” constants for the functional approximation g(y) = k / y – c
-- k = slope · c = horizontal shift (tune if your sales distribution changes)
VAR _magic_k = 0.6
VAR _magic_c = 0.9

-- Target cumulative-sales cut-offs for the classes (can be parameterised)
VAR _a_threshold = 0.50     -- top-A customers should cover 50 % of sales
VAR _b_threshold = 0.75     -- A + B together 75 %
VAR _c_threshold = 0.90     -- A + B + C together 90 %

-- Initial Z-score limits derived from the k/c approximation
VAR _z_a_threshold_initial = DIVIDE(_magic_k, _a_threshold) - _magic_c
VAR _z_b_threshold_initial = DIVIDE(_magic_k, _b_threshold) - _magic_c
VAR _z_c_threshold_initial = DIVIDE(_magic_k, _c_threshold) - _magic_c

/* ------------------------------------------------------------------------- */
/* Build a customer-level sales table                                         */
/* Adapt only the two variables below if your model uses other tables/fields */
/* ------------------------------------------------------------------------- */
VAR _customer_sales =
FILTER (
    ADDCOLUMNS (
        CALCULATETABLE (
            'Dim Customer',
            REMOVEFILTERS ( 'Dim Customer' ),
            CROSSFILTER ( 'Fact Sales'[Customer_SKey], 'Dim Customer'[Customer_SKey], BOTH )
        ),
        "@Sales", [Customer Sales]
    ),
    [@Sales] > 0
)

/* Total sales – needed once to adjust the Z-score limits */
VAR _all_sales =
CALCULATE ( [Customer Sales], REMOVEFILTERS ( 'Dim Customer' ) )

/* Mean, standard deviation and Z-score per customer */
VAR _stdev_sales = STDEVX.P ( _customer_sales, [@Sales] )
VAR _avg_sales   = AVERAGEX ( _customer_sales, [@Sales] )
VAR _z_score =
    ADDCOLUMNS (
        _customer_sales,
        "@z_score", DIVIDE ( [@Sales] - _avg_sales, _stdev_sales )
    )

/* ------------------------------------------------------------------------- */
/* One-shot adjustment of the Z-score limits                                 */
/* Using h(x₀,a,y) = (a / y)·(x₀ + c) – c from our functional approximation   */
/* ------------------------------------------------------------------------- */
VAR _a_threshold_actual =
    DIVIDE (
        SUMX ( FILTER ( _z_score, [@z_score] > _z_a_threshold_initial ), [@Sales] ),
        _all_sales
    )
VAR _z_a_threshold_adjusted =
    ( DIVIDE ( _a_threshold_actual, _a_threshold ) * ( _z_a_threshold_initial + _magic_c ) )
    - _magic_c

VAR _b_threshold_actual =
    DIVIDE (
        SUMX ( FILTER ( _z_score, [@z_score] > _z_b_threshold_initial ), [@Sales] ),
        _all_sales
    )
VAR _z_b_threshold_adjusted =
    ( DIVIDE ( _b_threshold_actual, _b_threshold ) * ( _z_b_threshold_initial + _magic_c ) )
    - _magic_c

VAR _c_threshold_actual =
    DIVIDE (
        SUMX ( FILTER ( _z_score, [@z_score] > _z_c_threshold_initial ), [@Sales] ),
        _all_sales
    )
VAR _z_c_threshold_adjusted =
    ( DIVIDE ( _c_threshold_actual, _c_threshold ) * ( _z_c_threshold_initial + _magic_c ) )
    - _magic_c

/* Z-score of the current customer row */
VAR _my_z_score =
    DIVIDE ( [Customer Sales] - _avg_sales, _stdev_sales )

/* ------------------------------------------------------------------------- */
/* Final classification                                                      */
/* ------------------------------------------------------------------------- */
RETURN
    SWITCH (
        TRUE (),
        _my_z_score > _z_a_threshold_adjusted, "A",
        _my_z_score > _z_b_threshold_adjusted, "B",
        _my_z_score > _z_c_threshold_adjusted, "C",
        "D"
    )