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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors