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

[PipeRey]

Hi everyone,

I'm trying to create a running sum measure in DAX that changes dynamically based on the applied filters. The running sum should accumulate values according to the rank of each row. I've already created separate tables, and they work as expected. However, when I attempt to implement this using a measure with virtual tables, it doesn't produce the desired result.

Here's the code I'm working with. Each part seems to work independently (e.g., ranking and filtering), but as a combined measure, it fails to return the correct running sum per rank.

Running_sum_measure =

VAR tbl_1 =

    ADDCOLUMNS(

    FILTER(ALL(sales_table[CodigoArticulo]),[CQ1]>0)

    ,"porc"

    ,[PorcentajeParticipacionCQ1]

)

VAR tbl_1_sorted =

    ADDCOLUMNS(

        tbl_1,

        "Rank",

            RANKX(

                tbl_1,

                [porc] + RAND() * 0.0001, // Adding a slightly larger random component to ensure uniqueness

                ,

                DESC,

                Dense

            )

    )

VAR table_rank =

    ADDCOLUMNS(

        tbl_1_sorted,

        // Columna "RowNumber" para asignar un número de fila único a cada registro

        "RowNumber",

            RANKX(

                ALL(tbl_1_sorted), // Considera todos los registros de la tabla sin filtros

                [Rank] +

                RANKX(

                    ALL(tbl_1_sorted),

                    CALCULATE(

                        MAXX(tbl_1_sorted, tbl_1_sorted[CodigoArticulo]) // Valor máximo de CodigoArticulo para el desempate

                    ),

                    ,

                    ASC,

                    Dense // Usa Dense para clasificaciones consecutivas

                ) / COUNTROWS(ALL(tbl_1_sorted)), // Ajuste para evitar empates basado en el total de filas

                ,

                ASC,

                Dense // Usa clasificación ascendente y consecutiva para "RowNumber"

            )

    )

VAR final_ =

    ADDCOLUMNS(

        SUMMARIZE(

            table_rank,

            table_rank[CodigoArticulo],

            table_rank[RowNumber],

            "porc", SUM(table_rank[porc])

        ),

        "RunningSum",

            VAR CurrentRank = [RowNumber]

            RETURN

                CALCULATE(

                    SUM(table_rank[porc]),

                    FILTER(

                        ALL(table_rank),

                        table_rank[RowNumber] <= CurrentRank

                            && table_rank[CodigoArticulo] = [CodigoArticulo]

                    )

                )

    )

VAR running_current = SUMX(final_,[RunningSum])

RETURN

    running_current
the initital table will look something like this

CodigoArticulo	porc
abc234hj21	0.001269819292983
bc234hj21	0.023401928347459
bc234hj223	0.032123394040404
bc234hj24gn	0.011001110932900

If anyone has experience with handling ranks with tie-breakers in a measure like this, I’d appreciate any advice on how to make this running sum work dynamically with filters and rankings.

Thank you in advance for any help you can provide!

[Poojara_D12 · Accepted solution]

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