Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |