Create a Running Total by Category in Power Query
ฝัง
- เผยแพร่เมื่อ 30 มิ.ย. 2024
- Want a running total that resets for different categories? This video shows how you can create grouped running totals.
WRITTEN ARTICLE
gorilla.bi/power-query/runnin...
Master Functions and Syntax in M
powerquery.how
CONTENTS
00:00 Introduction
00:44 Running Total List.Generate
01:40 Create Running Total Function
06:36 Apply Running Total Function
08:26 Running Total by Group
ABOUT BI Gorilla:
BI Gorilla shares videos and articles on Power BI and Excel to help you improve your skills.
Website: gorilla.bi
SUBSCRIBE TO MY CHANNEL
th-cam.com/users/bigorilla?sub_con...
LET'S CONNECT:
Blog: gorilla.bi
Facebook: / bigorilla
Twitter: / rickmaurinus
LinkedIn: / rickmaurinus
Thank you for your support!
#RunningTotal #powerquery #bigorilla
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!
Merci beaucoup!
Thanks, I've tried combining a group function with the running total, not much success ; So put say;
(Gfunc)=> let
Grouping = Table.Group(Gfunc {"Item"}, {{"Count", each _ }} )
in Gfunc
With a Running total function so all in one hit, the probem child is the grouped All Rows. my fav rtc is ;
List.Skip(
List.Accumulate( Source, {0}, (s,c)=> s & {List.Last(s) + c} ),1) , don't know how this compares with generate?
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".
You’re right Mike. You can also take any of the methods in this article:
gorilla.bi/power-query/running-total/
The first two are muchhhhhhh easier. However, they’re also slow 🥁 hence I’m teaching this one
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
Wow, that's amazing! I'm so glad you found my videos helpful and that you took the time to really dive into them. It sounds like you've been having a lot of fun learning, and I'm glad I could be a part of that. And thanks for watching my videos and giving me a feedback! 😁😁
Your pedagogic skills, the content, and the structure of these videos - everything is so well thought! Thank you!!
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.
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.
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!!!
Hola Raymundo 🙌🏻
Thank you very much for the elaborate comment. From what I can tell, you have the right attitude to pick up these techniques. And even though some of them may still be foreign, or don't come naturally, I hope you stick to it.
You reach a level at some point where you consolidate so many different approaches. And when you reach that point you can juggle together all kinds of solutions. Truth be told this one is a bit more advanced, and it took me a bit to figure it out too. Especially keeping the data types intact.
Just keep at it and before you know it you'll be making up these kind of solutions yourself 😁
Enjoy!
That was awesome, thanks Rick! Got my work problem sorted and working, now need to sit down and fully understand this great solution.
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!
Thanks you very much Stefano. I really enjoyed creating the video and laying out the groundworks. Happy you found it valuable 🚀🚀
Excellent content! It helps me a lot. With your concise explanation and examples. Thank you so much
Just what I needed!
A classic topic, will for sure watch this video 4 times per year :-) , thanks!
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!!!!
Your message definitely made my day Marcelo. Thanks a lot. This topic isn't easy, but I'm glad the video helped!
This is FANTASTIC - Thanks so much!
Amazing!! 5 stars!! It helped me a lot, thanks
Wow I learnt a lot and just solved a major problem I currently have. Thank you.
It was amazing!!! You're an excellent teacher!!!
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...
Your are the BI running total genius!
Thank you!
You are really amazing. Thank you so much for sharing this trick, it is really saving a lot of time.
Great Job. Always love on how you solve the problem. Perfect Explanation
Thank you so much for the kind words! I'm glad you enjoyed my problem-solving and found the explanations to be helpful. That's exactly what I aim for in my videos, so it's great to hear that it's resonating with you. And thanks for being a fan of my videos!
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
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...
Thank you for the video ! It is brilliant !
Wish I could give you 5 thumbs up. This was so helpful, thank you!
This is incredible, thank you!
This helps a lot, thank you! :)
Thank you for the video. Very useful information and a perfect explanation 🤓
Thank you! Love making these videos 😁
Thank you for the great info shared.
Thank you very much. This is very helpful
man you deliver!
Thank you very much, these methods are useful for so many transformations. Hope you picked up something here!
Great video, thank you! :)
brilliant, will try it, TYSM!
Pure genius!
Thank you!
Really good video - thanks a lot, I learned a ton from you :)
So glad to hear that, thanks Rui!
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
Unbelievable rightt. However, do note that you can do this much easier using the List.Range or List.FirstN function (as explained here: gorilla.bi/power-query/running-total/ )
The method in the video, however, is preferred, with regards to performance.
@@BIGorillathanks so much for replying to me... really appreciate it. I will check the "easier" way (again I come from a world where this is really a matter of 2 clicks lol)
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!
Neat and super fast RT calc.
🤯🤯🤯🤯🤯🤯🤯🤯
Great effort, you are awesome
Amazing! Thanks for explaining the logic that's easy to understand.
Like & Subscribe
Thanks tx, this was a tough one, but very effective!🦉
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.
Really great video, I wanted to group the running total of last 52 weeks data, how do i achieve this?
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.
what are the pros and cons of doing it using dax vs what you are doing by adding a column in the query editor?
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.
I suspect your Running Total does something like: Current Value + Next Value.
When adding 'null' to any value, the result is null. So that means a running total that includes null values, will return null once it passed this value.
You can fix this by using List.Sum( { CurrentValue, NextValue } ) instead of using CurrentValue + NextValue. The List.Sum function ignores nulls and still sums.
More details in this article, which has a similar issue for concatenating.
gorilla.bi/power-query/concatenate-values/
@@BIGorilla After going through your videos a second time I was able to make the running totals work properly and verify the functionality with the addition of new data. Thank you!
👍💯
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.
I have found error root. I was sorting the data with wrong column, which is duplicate of month/year and then change its type to date. This new date column is not working for sorting. I re-sorted the data with year and month separately and it works
Very helpful thanks. However I need to calculate rolling 12 months by category. Can you help me to do that please?
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!!
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 :)
TinySparrow, Thanks for the suggestion. Some videos are indeed useful for Power Query and others for general Power BI. My title includes Power Query for this video and the tags include both Power BI and Power Query. If that's what you were suggesting, nothing to change ;-)
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.
Not sure, it may be one of the references to the table parameters that's incorrect. Hard to tell without a working example.
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.
Hi Mohammed. I currently have playlists for Power BI, Power Query and DAX. Have you seen those?
@@BIGorilla Thanks a lot for your valuable time replying to my comment. Do you mean it is already organized? I am sorry that my word was impolite.
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
Hey Anthony. Thanks, glad to see it’s helping out. I never thought of backgrounds from general pq teacher. Myself I studied accounting & business control; but this became a hobby :)
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?
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
it was hard to do it but i did it
Can you help me with how to calculate running total of multiple columns
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
Hi Mariusz, I don't understand you question. This method is focused on the Power Query M Language, Measures/DAX are not involved.
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.
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?
That’s unusual. Please check if the steps in your query editor are in chronological order. It sounds as if your last step does not pick up the running total step
@@BIGorilla Hi, I get the exact same issue while I can confirm I have performed each exact step as hte ones shown in your video. RT column with correct running total appear att the query editor level but once I click on "Close & Apply", this column becomes empty and I cannot create any chart. When I click on close and apply, I get a and error message although the query is loaded but no message (or dont know where to find) on what/where is the mistake. Any idea? I have spent a lot of time on that and still no clue. Thanks :)
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”
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 ?
Interesting. What would you do once a line reaches 1000?
Let's say your example resets when you reach 20. And you have below data.
a 10
b 15
c 5
d 5
b would have a running total of 25. So 20, and then if it resets it gets to 5.
So which category would b fall into? Is it the first group? Or the second? And how would you treat the 5 that exceeds the 20?
Is it not easier to do it with Dax with Time Intelligence function? Same results?
Batman - Good question. Performing a running total is relatively easy in DAX. If that solves your needs, go ahead.
There are cases where people want to perform a running total for some intermediary calculations, or perhaps they want numbers precomputed, so that when they are loaded into the data model the compression is optimal.
Regardless of the case, there are reasons why some want to do it in #powerquery. You ought to make that judgement call. I've had a lot of people ask me how to perform running totals by group. And in those cases where you need it in Power Query, this is a good way.
Enjoy!
@@BIGorilla Thanks for your answer! Keep it up, your videos are very usefull
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
I want to know too, not sure yet!
looks needlessly complicated. we clearly hit (severe) limitations of powerbi for data analytics.
Bigbarry- M is a query language. Running totals tend to be better made in DAX.
Also, There’s plenty of easier ways to do it in M You can use List.Range or List.FirstN with List.Sum. Much easier to understand. But also performance much less.
TOP!!! #powerbinareal