cancel
Showing results for
Did you mean:
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
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/

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

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

Proud to be a Super User!

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 🙂
9 REPLIES 9
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?

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.

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.

Proud to be a Super User!

Community Champion

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

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)

New Member

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

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

Proud to be a Super User!

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 🙂
Regular Visitor

The numbers seem to be off slightly with this.

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

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.

Proud to be a Super User!

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/

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

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

Proud to be a Super User!

Announcements