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.
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |