Running total chart

[tmullady]

Hello,

I wanted to see if someone could help me create a running total chart. My fields are the following

DATE , RANGE, CONTRIBUTIONS

Any help would be greatly appreciated.

Thanks,

TIm

[Greg_Deckler · Accepted solution]

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/

[Rakeshlipare]

i have plotted bar chart with running total and created a tooltip for it but i am unable to see the exact rows in tooltip as the number present on the bar.

The tooltip  is for the orange bar having 6 number .

[OscJara]

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?

[KHorseman]

@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.

[Sean]

@OscJara Because you are working with week on week a date table will not help you

Read this solution here...

http://community.powerbi.com/t5/Desktop/Calculate-Difference-from-Previous-Date-Dynamically/m-p/3427...

Yes @KHorseman 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)

[OscJara]

thanks @KHorseman and @Sean... I could do the running the total using Date as X axis (top chart using lines) but I am still stuck with the running total using weeks as X axis (below chart using bars).

thanks again and I am really apreciating your help

[KHorseman]

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])

[austinsense · Accepted solution]

Running total usually looks like this ...

CALCULATE ( SUM(TABLE[Contributions]), FILTER( ALL(DATE), DATE[Date] <= MAX(DATE[Date] ) )

[tmullady]

The numbers seem to be off slightly with this.

 CALCULATE(SUM(Query20[NET_CONTRIBUTIONS]),FILTER(ALL(Query20[DATE]),Query20[DATE] <=MAX(Query20[DATE])))

[KHorseman]

Note that in the examples the last part with the filter for dates is filtering a separate date table. You need to add another table to your data model for this kind of time intelligence.

[Greg_Deckler · Accepted solution]

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/