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 PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

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