Create a Running Total by Category in Power Query

Just what I needed!
A classic topic, will for sure watch this video 4 times per year :-) , thanks!

Thanks for the video... I came from Tableau word and this can be all done with a click of a mouse... I can't believe Power BI/Query requires a 17 minute video for a simple table calculation... it blows my mind

what are the pros and cons of doing it using dax vs what you are doing by adding a column in the query editor?

Wish I could give you 5 thumbs up. This was so helpful, thank you!

It was amazing!!! You're an excellent teacher!!!

it was hard to do it but i did it

Amazing!! 5 stars!! It helped me a lot, thanks

You are really amazing. Thank you so much for sharing this trick, it is really saving a lot of time.

Hi @BIGorilla Just One question: if some of the groups don't have values for some months when you try to plot them together on a column chart the Total of different Running Totals Will be wrong, this because some of the categories have no data for some months and Power query don't sum values that don't exist. How can be fixed? Thanks

Thanks Rick, against you make my day. I have to to calculate the amount of sales for the last 3 weeks, the last 6 weeks and so on. And i will start with your solution...

Hi, you have given a very nice explanation of running total.could you please help me in one problem that is,
i am plotting stack chart of running count of categories with respect to date and using the same categories column as legends but in last month i don't have particular category and this running count is not carry forwarding the previous month value. so how can i do it.

i am asking you this because this query is bit related to running sum and one loophole you mentioned in the last of one video.

Your pedagogic skills, the content, and the structure of these videos - everything is so well thought! Thank you!!

Hi, I am getting following error while applying function : Expression.Error: We cannot apply field access to the type Text.
Details:
Value=Running Total
Key=Details Please if you can help to get this solved
It was fine upto below step
= Table.Group(#"Changed Type1", {"Product"}, {{"Details", each _, type table [Month=nullable date, Product=text, TQty=nullable number]}})

Error in last step
= Table.AddColumn(#"Grouped Rows", "Custom", each fXRunningTotalperItem("Running Total"[Details],"Amount"))

Hello Rick, i have a request to this fascinating Function. I tweaked it to add a column with the Max Value. I did this with a List.Max function. It does work but I need to enter manually the name of the column. Is there a way to have the name of the column in a dynamic way? Not sure I am very clear so here the code. In line 23, I’d like the (Consolidation[YtDReversed]) To be dynamic, since i already entered "YtDReversed" when i called the function, it is the name of the accumulated data i needed.. Does it make sense? I can send my file also.
“let
1. Source = (RT_Name as text, MyTable as table, RT_ColumnName as text ) =>


2. let
3. Source = MyTable,
4. BufferedValues = List.Buffer (Table.Column ( MyTable , RT_ColumnName )),
5. RunningTotalList = List.Generate (
6. // Start value: Set RT to 1st value in BuffValues and set RowIndex to 0
7. () => [ RT = BufferedValues{0}, RowIndex = 0 ],
8. // Condition: Generate values as long as RowIndex is < than number of list items
9. each [RowIndex] < List.Count( BufferedValues ),
10. // Record with logic for next values:
11. each [
12. // Calculate running total by adding current RT value and the next value
13. RT = List.Sum( { [RT], BufferedValues{[RowIndex] + 1} } ),
14. // Increment the RowIndex by 1 for the next iteration
15. RowIndex = [RowIndex] + 1
16. ],
17. // Only return the running total (RT) value for each row
18. each [RT]
19. ),
20. Consolidation = Table.FromColumns (
21. Table.ToColumns (Source) & { Value.ReplaceType ( RunningTotalList ,type {number} ) },
22. Table.ColumnNames (Source) & {RT_Name} ),
23. Add_Max = Table.AddColumn ( Consolidation, "Max", each List.Max ( Consolidation[YtDReversed]), type number)

24. in
25. Add_Max
26. in
27. Source”

Really appreciate how this, and the previous video, explained the concept, step by step, to get a running total by categories. I now understand the solution and have applied it to my project. Much better than just cut and pasting a solution. The written articles was also referenced, as here the code is annotated.

Great video, thank you! :)

This helps a lot, thank you! :)

This is FANTASTIC - Thanks so much!

Hello Rick, I've tried this, but when I group the table, the sorting of the dates just disappears. I solved it by putting the Table.Sort in Table.Buffer. Hope it helps somebody.
Other than that, great video and a great explanation.

Really great video, I wanted to group the running total of last 52 weeks data, how do i achieve this?

Thank you for the great info shared.

Again a great and clear explanation of a topic I was looking for. I'll have to watch this video a few more times before I understand the building of a function. But it's definitely worth it. Thank you.

Works perfectly in power query editor but when I try to establish the same table on a report all values from running total column disappear. Any clue how to problem solve this?

Thank you for the video ! It is brilliant !

I rick, thanks for your video serie, i'm just seeing the best powerquery teacher (that includes you in my view) all seems to have some kind of mathematical background, is this your case..? is this required from your point of view ? Because i feel like having a base understanding of vectors and matrices is critical to understand the way list and table work under the hood

Neat and super fast RT calc.

Do you have a video that explains how to reset running total based on a condition within sub category. For example, if the code is to be used for stock with weighted average where closing qty and closing amount (COGS of closing qty) should be displayed for the items purchased recently.

This is SO complicated for such a simple calculation. Not criticizing the teaching here, which is very clear and helpful. Rather, I don't get why Microsoft doesn't just have a button for "calculate running total by group".

Great video, worth watching for the tip on using Table.Column, I'd given up on a table [Column] ,
as variables in function, I have been using this simpler version just invoked on the Grouped Column,
not as flexible but does work,
(RTF)=>
let
Custom1 = List.Generate( ()=> [ RT = RTF [Unit] {0} , Counter = 0] ,
each [Counter] < Table.RowCount( RTF ) ,
each [RT = [RT] + RTF [Unit] {[Counter] + 1 }, Counter = [Counter]+1 ] ,
each [RT] ),
Custom2 = Table.FromColumns(
Table.ToColumns(RTF ) & { Custom1 } , Table.ColumnNames(RTF) & {"RTC"} )
in
Custom2

In my data set, everything works great through the Grouped Rows step. After that, when looking at the running totals, it works properly for the first group, then returns "Null" for the rest of the groups, then repeats the list of groups, totals properly, nulls the rest of the groups, and repeats with no end.

Hello,
Is it possible to put measures in rows of matrix but in row will be also other variable and measures will be above this variable? Thanks in advance

Not sure if you are looking at your comments, but I thought I'd give some advice. You should probably go and add both Power Query and Power Bi to all your videos that you can. Some will be specific to Power Bi, but I know you have videos that can be used for PQ as well. Just a thought :)

Your are the BI running total genius!

Can you help me with how to calculate running total of multiple columns

If I want to group this items based on value of running total not for category how can I do this ? I mean if i want to split this items into groups where running total for each category doesn't exceed 1000 and when it reach this limit we put the next items in another group untill they reach 1000 in running total and so on ?

Excellent. Thanks Rick for your always excellent videos You are really a big brother in helping us understand M code. 👍👍

I started today with your post on List.Generate which led me to your running totals videos.

3 hours later and at least 30 clicks of the pause button and another 10 or so rewinds, I'm done.

All three videos were great.

Watching the evolution of running totals from List.FirstN to the function used to group by category really helped it sink in.

Thanks

Pure genius!

Great Job. Always love on how you solve the problem. Perfect Explanation

Amazing! Thanks for explaining the logic that's easy to understand.
Like & Subscribe

👍💯

I'm surprised with the didactic you gave us on this video!!! Fantastic!!! I used to use this same formula, but I couldn't understand the steps!!! I think I still cannot make it myself, but now I comprehend what I'm writing and expectations upon the results are clearer! Thank you so much!!!!

Thank you for the video. Very useful information and a perfect explanation 🤓

Really good video - thanks a lot, I learned a ton from you :)

Hi when is your next video coming

Is it not easier to do it with Dax with Time Intelligence function? Same results?