Cumulative sum in calculated column without CALCULATE()

user10456 — Mon, 30 Oct 2023 01:33:39 GMT

I want to summarise a table in my data model, and then add a cumulative count column which I can use for further analysis. I need this to be a calculated column rather than a measure. Currently, I am using SUMMARIZE() to create one table, and then using CALCULATE() and EARLIER() to create another table based on it. I would like to summarise the other table and create the running total in the same table, or otherwise not add tables to the data model. (Currently I am hiding the intermediate table, but I will have to hand this over to someone else and I'd rather avoid that if I can).

DAX for example tables

Raw Data = DATATABLE("Type",STRING,"Day",INTEGER,{
  {"A",1},{"A",1},{"A",2},{"A",4},{"A",7},{"B",2},{"B",2},{"B",3}
})

Intermediate Table = SUMMARIZE('Raw Data', 'Raw Data'[Day], 'Raw Data'[Type],
  "Count", count('Raw Data'[Day])) 

Running Total = ADDCOLUMNS('Intermediate Table', "Running Total", 
  CALCULATE(SUM('Intermediate Table'[Count]), ALL('Intermediate Table'), 
  'Intermediate Table'[Day] <= EARLIER('Intermediate Table'[Day]), 
  'Intermediate Table'[Type] = EARLIER('Intermediate Table'[Type])))

Re: Cumulative sum in calculated column without CALCULATE()

FreemanZ — Mon, 30 Oct 2023 02:08:05 GMT

hi user10456 ,

not sure if i fully get you, try like:

table = VAR _table1 = ADDCOLUMNS( SUMMARIZE( 'Raw Data', 'Raw Data'[Day], 'Raw Data'[Type] ), "Count", CALCULATE(COUNTROWS('Raw Data')) ) VAR _table2 = ADDCOLUMNS( _table1, "Running Total", SUMX( FILTER(_table1, [Day]<=EARLIER([Day])&&[Type]=EARLIER([Type]) ), [Count] ) ) return _table2

it worked like:

topic Re: Cumulative sum in calculated column without CALCULATE() in DAX Commands and Tips

Cumulative sum in calculated column without CALCULATE()

Re: Cumulative sum in calculated column without CALCULATE()