Forum Discussion
Problem with Cumulative Total column
Stuck trying to figure out how to insert a calculated column... below is my code but it's only returning the values that already exist in the column May16
Column = CALCULATE(SUM(Sheet1[May16]),FILTER(ALL(Sheet1[Day of Month]),Sheet1[Day of Month]<= MAX (Sheet1[Day of Month])))
Day of Month is just a column with the day of the month number so May 31st = 31
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
9 Replies
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....
I get an error saying SUMX not recognizedHere is my table
But I see in your pasted screen shot it has check mark and 'no syntax problem detected'....
your interface is different than mine - you are in Query Editor
try this - in Desktop, not query editor but just regular Data view (icon in left frame), select Table in Fields frame right, then in Modeling tab there is the 'New Column' choice.... and one offers the expression field just below the ribbon....
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
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
