Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative Running Total on virtual Table

Hi Team,   I have created a virtual table using DAX, I need to calculate a new column with the cumulative total using the virtual table.     Kindly suggest possibilities.   Thanks in Advance, ...
  • mahoneypat's avatar
    4 years ago

    It may be better to do this with a measure instead of generated a running total in a variable, but here is an example that shows how to approach this.  The first variable is just a table with Index and Value, and the 2nd variable shows how to add the RT column (you'll need a column that has a date, index, etc. to use for filtering).  This is a table expression but you could return a scalar variable to return a calculation with the added column as a measure.

     

    DemoTable =
    VAR vTable =
        DATATABLE (
            "Index"INTEGER,
            "Value"INTEGER,
            {
                12 },
                24 },
                36 }
            }
        )
    VAR vAddRT =
        ADDCOLUMNS (
            vTable,
            "RT",
                VAR thisrowindex = [Index]
                RETURN
                    SUMX ( FILTER ( vTable, [Index] <= thisrowindex ), [Value] )
        )
    RETURN
        vAddRT

     

    Pat