Forum Discussion
Running total chart
- 10 years ago
Not much to go on, but you should be able to use the Cumulative Total pattern here I believe:
http://www.daxpatterns.com/cumulative-total/
- 10 years ago
Running total usually looks like this ...
CALCULATE ( SUM(TABLE[Contributions]), FILTER( ALL(DATE), DATE[Date] <= MAX(DATE[Date] ) )
Hello Everyone
I am very new using Power BI and I am trying to follow your suggestions but I am still stuck with the total running values.
For ACC AVGPoints I have:
ACC AVGPoints =
CALCULATE(
SUM(TResponses[AVG Points]),
FILTER(
ALL(TResponses[Date of Activity - Valida]),
TResponses[Date of Activity - Valida] <= MAX(TResponses[Date of Activity - Valida])),
VALUES(TResponses[AVG Points])
)
But still, its only summing the points by the week and not accumulating total. What should I do?
- Anonymous10 years agoNot applicable
OscJara as I pointed out earlier, the examples given here use a separate date table. tmullady's last formula was incorrect because it is referring to dates in the same table, not using a relationship to a dedicated date table as the examples showed. You're doing the same thing.
In your formula, this argument:
MAX(TResponses[Date of Activity - Valida])
...returns the maximum date of activity in the current filtering context, not the maximum date overall. Maybe it would help to see how the two formulas translate to plain English.
Code: ACC AVGPoints = CALCULATE( SUM(TResponses[AVG Points]), FILTER( ALL(TResponses[Date of Activity - Valida]), TResponses[Date of Activity - Valida] <= MAX(TResponses[Date of Activity - Valida]) ), VALUES(TResponses[AVG Points]) Translation: ACC AVGPoints = give me the sum of TResponses[AVG Points] for all rows that match... ...the set of all rows in the entire Date of Activity - Valida column... ...where the Date of Activity - Valida is less than or equal to the latest Date of Actvity - Valida
in the current filter context... ...VALUES(TResponses[AVG Points]) just returns a column of all unique rows from that column
and I don't think it really accomplishes anything here because those values are already called
by the SUM at the beginning.I believe the problem is that because the filter context is coming from the OrderWeek column on the same table, you're implicitly passing another filter statement to that formula that essentially says, "Also filter TResponses to only the rows matching the current OrderWeek context on this chart" which effectively nullifies the ALL part of your formula.
Read the cumulative total article linked above and look at how it's done there. The example works if you do it the way the example demonstrates.
- Sean10 years agoCommunity Champion
OscJara Because you are working with week on week a date table will not help you
Read this solution here...
Yes Anonymous is absolutely right get rid of VALUES(...
Also you have an additional field in the Legend (breaking down the running total by something which will need to be addressed)
- Anonymous10 years agoNot applicable
Adding a week column to a date table is not a problem. There are lots of examples of custom date tables with weeks.
The easiest way is to add a custom column in the query editor that gives the date of the end of the week. The formula is = Date.EndOfWeek([Date])