Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |