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.
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.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |