Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello PowerBI experts!
How to calculate n days sum of all running totals using measure? For an example I have such table and I want caculate what would be the sum of all running total values in prevous days in day 10 (150). This would be something like running total of running total:
Values in column "Running total" is calculated using formula:
Running Total:=CALCULATE(SUM(fct[Amount]); FILTER(ALL(dimDays);dimDays[DayId]<=MAX(dimDays[DayId]))
Solved! Go to Solution.
Hi @nauriso1,
Please try these two measures
Running Total = CALCULATE(SUM(fct[Amount]),fILTER(ALL(fct),'fct'[DayID]<=MAX('fct'[DayID])))+0
and
Sum Running Total = CALCULATE( SUMX( FILTER( ALL('fct'), 'fct'[DayID]<=MAX('fct'[DayID]) ), [Running Total]) )
Hi @nauriso1
I used this for my running total (as a calculated column on fct)
Running Total = CALCULATE( SUM( fct[Amount]), FILTER( ALL('fct'), 'fct'[DayID]<=EARLIER('fct'[DayID]) ) )
And this for the Sum of Running Total
Sum of Running Totals = CALCULATE( SUM(fct[Running Total]), FILTER( ALL('fct'), 'fct'[DayID]<=EARLIER('fct'[DayID]) ) )
This was my result
Thanks Phil for your answer, but I need measure not calculated column.
Hi @nauriso1,
Please try these two measures
Running Total = CALCULATE(SUM(fct[Amount]),fILTER(ALL(fct),'fct'[DayID]<=MAX('fct'[DayID])))+0
and
Sum Running Total = CALCULATE( SUMX( FILTER( ALL('fct'), 'fct'[DayID]<=MAX('fct'[DayID]) ), [Running Total]) )
Thanks @Phil_Seamark, that is what I was looking for. One question, why did you add a zero at the end of the Running Total formula?
Hi @nauriso1,
Just to force it to return a result for the day for the grid-visual I was using it. It doesn't affect the formula, so feel free to remove if you prefer.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |