Forum Discussion
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,
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,
{
{ 1, 2 },
{ 2, 4 },
{ 3, 6 }
}
)
VAR vAddRT =
ADDCOLUMNS (
vTable,
"RT",
VAR thisrowindex = [Index]
RETURN
SUMX ( FILTER ( vTable, [Index] <= thisrowindex ), [Value] )
)
RETURN
vAddRTPat
2 Replies
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,
{
{ 1, 2 },
{ 2, 4 },
{ 3, 6 }
}
)
VAR vAddRT =
ADDCOLUMNS (
vTable,
"RT",
VAR thisrowindex = [Index]
RETURN
SUMX ( FILTER ( vTable, [Index] <= thisrowindex ), [Value] )
)
RETURN
vAddRTPat
mahoneypat Although you posted this three years ago, many thanks! I had been searching for this answer for hours!
