Create a Running Total by Category in Power Query 

Want to read the written article? You can find it here: gorilla.bi/power-query/running-total-by-category/ And in case you need to brush up how Running Totals work, the foundations you can find here: gorilla.bi/power-query/running-total/ Enjoy Power Query!

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.

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

Dude, you saved my life 😂 awesome video I also really like that you gave the m-code of your previous video as a summary to start this video - saved me a lot of time so I didnt have to watch the previous fully if I didnt want to

Excellent as always. I love that you always tackle the more complex problems.

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.

That was awesome, thanks Rick! Got my work problem sorted and working, now need to sit down and fully understand this great solution.

Excellent content! It helps me a lot. With your concise explanation and examples. Thank you so much

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

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

Ciao, thank you from a Power Query student, the lesson that you shared was - once again - clear and helpful. It's very difficult to express certain processes and this makes your work expecially note worthy. Spot on and many things!

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

This is FANTASTIC - Thanks so much!

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

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!!!!

I'm seeing the steps you are applying and everything makes sense, you even make it look very easy, but for me to reach that solution, I would have to have a privileged mind like yours, to have that logic and reach the solution. And not only do you teach us the solution, you expand our minds to apply what we have learned in other problems. Greetings from Mexico!!!

Wow I learnt a lot and just solved a major problem I currently have. Thank you.

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

Your are the BI running total genius!

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

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

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

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

Subscribed and Thumbs Up This is awesome. I like it how you explain this in a structured and easy to follow way. Voice is also clear and not boring. Will check the rest of your videos...

This is incredible, thank you!

Thank you for the video ! It is brilliant !

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

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

Thank you very much. This is very helpful

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

Thank you for the great info shared.

This helps a lot, thank you! :)

brilliant, will try it, TYSM!

Pure genius!

Great video, thank you! :)

Neat and super fast RT calc.

man you deliver!

Absolutely fantastic solution and so well explained. However I am struggling with it a bit with and 8 Million record fact table, I am currently experimenting with including a a buffered table right before the grouping - then referencing buffered table in the next two steps

You're the Best!

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

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

Hi Rick, please can you give your advice on this. I note in the previous video, which i used the code for the start of this one you actually use the index function (can be seen in the advanced editor) rather than the count function that’s in the previous video. My data set is rather large (1.6m rows with a fair few steps before the buffered values step)and i am not getting the values i expect. Could it be that i should use index over counter and if so how can i apply. An updated version of this video would be amazing if there is now a better way. Thanks in advance.

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.

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.

🤯🤯🤯🤯🤯🤯🤯🤯 Great effort, you are awesome

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

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

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

Very helpful thanks. However I need to calculate rolling 12 months by category. Can you help me to do that please?

If I do a merge query(referencing another table to current) on an table after adding RT Column, then the RT column data changes to the Last RT balance of that category. Can you check?

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 for nice explanation. I followedstep by step and successfully created the Running Total column with correct figures in the Power Query. However, when I close and apply, the running total is showing zero in the Power BI Report View or Table View.

Hi Rick, I‘m facing a problem right now. After creating the function and trying to create the new column, my current table was brought back to its beginning look, so every step that was done before (name changing, type changing etc.) has been „removed“. But the biggest problem I‘m facing is, that all columns, except the newly created one „running total“ have been moved down one row. If I now use the function „Apply first row as header“ it tales the first value of „running total“ as the new header. Is there a way to overcome this issue? Thanks!!

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.

Thanks a lot for this. I'm trying to invoke a calculation for rolling 4 quarters sum by group. But I'm getting stuck. My rolling sum function won't work. Any tips? = (RS_Name as text, MyTable as table, RS_ColumnName as text) => let Source = MyTable, Bufferedvalues = List.Buffer(Table.Column(MyTable,RS_ColumnName)), Custom1 = Table.AddColumn(Source, "Rolling Sum", each if [Index]>=4 then List.Sum(List.Range(Bufferedvalues,[Index]-4,4)) else null) in Custom1 Cheers

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

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

👍💯

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

it was hard to do it but i did it

Hi Rick, your videos have been fantastic. I tried your function at 6:09 but got this error: "we cannot convert a value of type list to type function". Any idea? With thanks.

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

Thanks a lot for your videos. Please, can you organize all your videos on your youtube channel into playlists so everything is organized? I like your videos a lot.

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.

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”

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

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 ?

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?

i found the result when grouping by date and one category is different with the result when grouping by date and two categories

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

Hi when is your next video coming

looks needlessly complicated. we clearly hit (severe) limitations of powerbi for data analytics.

TOP!!! #powerbinareal