Forum Discussion
Problem with Cumulative Total column
Running = SUMX(
FILTER( Sheet1,
Sheet1[Day Of Month]<=EARLIER(Sheet1[Day Of Month])
),
Sheet1[Column With Amount])....didn't know the name of the column with the amount -- - so made that up....
if this doesn't work please copy/type in a brief sample of the table itself to be viewed....
Hi,
You can create a calculated column to do it. See my screenshots below.
Running = SUMX(FILTER( Sheet1, Sheet1[Day Of Month]<=EARLIER(Sheet1[Day Of Month])), Sheet1[Column With Amount])
Or you also can use a measure to achieve it.
Measure = CALCULATE(sum(Sheet1[Column With Amount]), FILTER(Sheet1, Sheet1[Day Of Month] <= MAX(Sheet1[Day Of Month])))
The difference between measure and calculated column is that a calculated column is just like any other column in a table and you can use it in rows, columns, filters, or values of a pivot table or any other report. While measure is an aggregated values from many rows in a table. The value of a calculated column is computed during data refresh and uses the current row as a context; A measure operates on aggregations of data defined by the current context.
For more details about calculated column and measure, you can see here.
BTW, for resolving “Sheet1” name issue, you may already have existing object named “Sheet1”. You should change it to another name.
Best Regards
Alex
This sounds suspiciously like something that should be done as a measure rather than a column. I'm not sure what sort of data structure would use a column for this.
Very new to BI... what is exactly is the difference between a measure and a calculated column? I know that i Can create the line graph I need with this column in my data. The cumulative total is the number of complaints so I can graph trendlines for different months.
a Calculated Column gets added to the fundamental data model table
while a Measure gets calculated on the fly of a visual (in this case a table visual)
To use a Measure:
Running2 = CALCULATE(SUM(Sheet1[Column With Amount]),
FILTER( ALL(Sheet1),
Sheet1[Day Of Month]<=MAX(Sheet1[Day Of Month])
)
)When significant row counts are involved there can be an impact on performance