Problem with Cumulative Total column

[Chollid1]

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

[CahabaData · Accepted solution]

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....

[AlexChen · Accepted solution]

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

[KHorseman]

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.

[Chollid1]

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.

[CahabaData]

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

[CahabaData · Accepted solution]

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....

[Chollid1]

I get an error saying SUMX not recognizedHere is my table

[Chollid1]

My table now looks like this as I'll need to only see the cumulative totals based on each month.

My future concern is what to do when it gets to today's date. In the current month I would want the cumulative total to stop after the last non 0 value. In this case September 8th would be NULL. Also, I did a find and replace on null with 0. I may need to put this back.

[CahabaData]

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....

[Chollid1]

If run this code from the desktop I get Failed to resolve name Sheet1. This may be from my new table format.