Help with Running Sum Measure Using Virtual Tables and Rank in DAX

Hi PipeRey 

To create a dynamic running sum in DAX that respects ranks with tie-breakers and responds dynamically to filters, it can be helpful to streamline the virtual tables and focus on minimizing row context complexity in the RunningSum calculation.

Here's an optimized version of your Running_sum_measure:

1. Separate Ranking Logic: The ranking process and the running sum should be kept as simple as possible to work in a measure context. Instead of introducing multiple RANKX calculations, you can directly rank the rows based on the porc value.
2. Running Sum Calculation: We can leverage the SUMX function directly on the virtual table to accumulate values based on the rank.

Here’s an alternative DAX code that should improve efficiency and correct any potential ranking conflicts:

Running_sum_measure =

VAR tbl_1 =
    ADDCOLUMNS(
        FILTER(ALL(sales_table[CodigoArticulo]), [CQ1] > 0),
        "porc", [PorcentajeParticipacionCQ1]
    )

// Step 1: Create a table with ranks based on the "porc" values
VAR tbl_1_sorted =
    ADDCOLUMNS(
        tbl_1,
        "Rank",
            RANKX(
                tbl_1,
                [porc] + RAND() * 0.0001, // Ensures unique rank with small random adjustment
                ,
                DESC,
                Dense
            )
    )

// Step 2: Calculate the Running Sum based on the "Rank"
VAR running_sum_table =
    ADDCOLUMNS(
        tbl_1_sorted,
        "RunningSum",
            VAR CurrentRank = [Rank]
            RETURN
                CALCULATE(
                    SUMX(
                        FILTER(tbl_1_sorted, [Rank] <= CurrentRank),
                        [porc]
                    )
                )
    )

// Step 3: Sum up the running sums for the current filter context
RETURN
    SUMX(running_sum_table, [RunningSum])

Explanation of Each Step:

1. tbl_1: This table filters sales_table by [CQ1] > 0 and adds the porc value for each item.

2. tbl_1_sorted: Adds a Rank column to tbl_1 by ranking porc values in descending order. Adding RAND() * 0.0001 ensures uniqueness and avoids ties.

3. running_sum_table: This virtual table adds a RunningSum column that calculates the cumulative sum of porc values based on the current Rank. It does this by summing porc values for ranks less than or equal to the current rank within the table.

4. Return Statement: The final SUMX aggregates the RunningSum values for the entire table in the current filter context.

This approach should yield a dynamic running sum that respects filters, while reducing complexity in ranking and tie-breaking. Let me know if it works as expected!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS