Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
perdigao18
Frequent Visitor

How to compute a running sum based on running count

Hi, I have a problem on computing a running sum based on a running count.

(note : I am working in data query) 

Consider this data sample to understand the behavior :

Code is a string,T is a number

RT code , RT code - 1 and RT class N are the desired columns

perdigao18_0-1600416071502.png

RT code is the running count of rows reset on each code. Then I apply a simple -1 to obtain RT code-1 and the last RT class N is the running sum of RT code -1 , only for flag = N , and reset by class. 

Following the standard formulas for commulative calculations I was able to compute the RT code

 

RT code
VAR TEMP = SUMMARIZE(CALCULATETABLE(Tabela,ALL(Tabela[code]),ALL(Tabela[T])),Tabela[code],Tabela[T])
VAR ClassesToSum = FILTER ( TEMP, Tabela[code]=selectedvalue(Tabela[code]) && Tabela[T]<=selectedvalue(Tabela[T]) )
Return CALCULATE ( COUNTROWS(ClassesToSum), ClassesToSum )

 

(Note that I am using TEMP table because in the context of my real problem my main table is very big with lots of columns. By creating this summarized table I am trying to improve the performance by focusing on the needed columns)

However I am not being able to compute the comulative sum of it with a similar logic.

The reset dimension is now class and my <= reference is the code, and it is a string, so I need something numeric to use with <= condition. So, I used the function value to create an auxiliary column that gives me the value of code as a number and then be able to filter the table always with the <= rows.

I tried the expression above, but it does not lead into the correct values and I am not even considering the flag value for now. 

RSUM =
VAR TEMP = ADDCOLUMNS(SUMMARIZE(CALCULATETABLE(Tabela,ALL(Tabela[code]),ALL(Tabela[T])),Tabela[code],Tabela[T]),"aux",VALUE(Tabela[code]))
VAR ClassesToSum = FILTER ( TEMP, [aux]<=value(selectedvalue(Tabela[code])) )
Return CALCULATE ( sumx(TEMP,[RCOUNT]), ClassesToSum )

Any tip?

0 REPLIES 0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.