cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tmullady
Regular Visitor

Running total chart

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

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User IV
Super User IV

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/

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

austinsense
Impactful Individual
Impactful Individual

Running total usually looks like this ...

 

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

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

9 REPLIES 9
OscJara
New Member

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?

ACC AVGPoints.png

 

 

KHorseman
Community Champion
Community Champion

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sean
Community Champion
Community Champion

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

RT - PoP.png

 

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

 

 

ACC AVGPoints by weeks.png

thanks again and I am really apreciating your help

KHorseman
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




austinsense
Impactful Individual
Impactful Individual

Running total usually looks like this ...

 

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

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

The numbers seem to be off slightly with this.

 

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

KHorseman
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Greg_Deckler
Super User IV
Super User IV

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/

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors