Create a Running Total by Category in Power Query

hey friends in this episode I'm gonna show you how to create running totals in power query but not just the regular ones we're going to look how to apply these on a group by group basis or category by category it's an exciting episode it's a difficult one but you're going to learn a lot and I'm sure you'll love it [Music] for those who don't know me my name is Rick I run the blog bi gorilla and I want to take you on a short Journey on how you can use running totals in power query it's a famous one for being very slow and in my last video you could see how you can make a very performing running total I will pick them from there to see how you can use that logic and actually apply it on groups if we're looking at my screen now we're going to start at where we ended last time so if I look at the query right here I'm looking at a running total created with list generate with all the benefits mentioned in my last video if you missed it make sure to check it out so this logic is it has a starting table I need to buffer my values I have a list generate function and I attach them again together to have your original table with a separate running total column this is the absolute fastest as I've made it so far the next exciting part is how can we make sure that we can apply this to different groups because you might actually want to have a running total that resets for each change in a group so on my screen here my first group might be jeans my second group might be involving the product skirts then we have socks and for each of these you might actually want to have a running total that runs until there and then restarts so how can you do that well the first step to do this is in it involves creating a function from your logic so we're going to look at that first so with this logic here we already know what this looks like I'm going to duplicate it and this is going to be the basis for our function called FX running total we want all of this to be get parameters so we can actually call it and apply it to our table and the best way to do that is to go to our Advanced editor and I'll zoom in a little bit here so what we're looking at here is all the logic that we need now how do you make this into a function to make this into a function we always start with parameters in the top and if we create a function let's imagine I'm a user I'm a user that wants to create a running total what do we need for this well first of all we're going to need a name so I'm going to call this the RT name running total name as text somebody will just fill in the text value here our next parameter it requires us to indicate the table where we want the running total so I'll call this my table as a table and lastly we also need to indicate at which of the columns our values are perhaps your table has multiple values so it's the amount column but perhaps you have a discount a net sales growth sales so we're going to need a column name here so I'll write here RT column name so we have an RT name running total name and a running total column name as text Now to create your function you write the equal and then the bigger than sign after it's a little funky but this indicates that the parameters are before this sign and the rest of the logic is after the sign okay and now we're going to go through the logic of what we need to adjust our function starts at line three here saying that our source table is called sales now we're going to already have something here so it's going to be called my table this is the table name people will have to give in my table parameter my table oh that's a nice pop-up okay what is the next step in my next step I want to find all the values in that table for my running total and instead of having to retrieve those values again and again I'm telling power query to keep it in memory with list buffer so this part here indicates which of the columns contains our our values now ideally we would do something like this and say I want to look at my table and within that table I want to have the RT column name now I can tell you that's not going to work I tried it before unfortunately this formatting is wrong but if you make functions you still need to be able to refer to these items so what can you do so instead of referencing this directly there is a function in power query gold table column and you can use that instead so writing table column the first argument is the table where you want to find the column which is called my table as we as an editing a parameter the second argument is the text value with the name now as input we already require text so all that we need is the RT column name that's it we close our parenthesis and this is our argument and this is fine the table column actually refers the values in a column as a list and that's exactly what the list buffer function needs so we're good to go here okay then we're going to generate the running total based on this list we don't need to change anything here because this part was already referencing the block values all that we need okay we continue then the next step here uh first of all it grabs our original table which is Source that's up there nothing needs to be changed then it grabs the values from the running total step also that will remain the same there's just one thing that we might want to change the second argument of table two columns um or table from columns here it requires us to also indicate the column name so we can change this one instead of hard coded and just write the RT column name no I'm wrong here we call this the RT name this is the name of our column perfect now I think we have gotten each of the steps already but we can test it later so with these adjustments if you click OK you'll find several things first of all we now have an fx running total function on the left whereas the previous queries looked over the table icon this one shows FX as if it's a function and now it also has all these items here and we can make documentation but for now we're just going to go with the example let's say we have this sill Square here how can we make use of this FX running total function well what you could do is you could make a new Step here and by default when you create a new Step this is the art the argument in the formula bar just refers to the previous step here which is a table so we start with the sorted rows table as shown now if we write here what we can do is use FX running total and the first step on running total function is the running total name so let's be obvious and just call our new column a running total then we go to the next argument the next argument is the table you want to create a running total for now it's referring to the sorted row step right here but it's good to be aware that this step is actually a table so we can leave it right here and say we're going to make a running total besides this table and the value to make a running total on is called it is within a column called amount so here you can write amounts foreign and basically that's the three arguments that we looked for so let's have a look if this works if you click on the side or on the confirm button here this is what happened so if we have a look at a previous step we just set a table which was sorted in the way that we wanted to and now if I click OK you'll find that we have 16 oh that was wrong we'll have 16. and then we have 25 which is the sum of these 51 which is the sum of these 3 6 86. so the only thing we have done now and that's what we wanted was we made a function that does our running table for us step number one this is working okay now we get to our next step because we still want to be able to do this on different groups so how can you achieve that let's have a look so I'm going to delete this this worked but it's not what I want to do right now okay so with this data set let's say we want to create a running total by group here okay oh I just sorted this in the wrong way I deleted it again there we go so if I want to have a look on how to do this by group I can first group my data click on the product click Ruby this automatically groups it on the product column and what I then want to do is use the all rows function which will give me all of the summarized rows in a table object there we go now each of these table objects here contains the rows that had the data so if I click next to it you'll find that these are the rows that were for genes and if I click next to the skirts one you'll find this you will probably see a preview but I'm having this bug in power bi which makes me zoom in now but normally if you click on the side it actually gives you a preview okay so far so good now we want to create a running total for each of these and one way to do it is the following we could go to a custom column and we could write again FX running total our column is called the running field and now for a table name our table is in the details column so you can write details and then also give a name to the value column which was called amount this is the column name with the values that is in this table object now I click ok and just by doing this I'm already getting a running total this is the preview so I'm already getting a running total here it's in here so as a separate step I have it let's see what happens so I already created it I'm going to remove well not this one I'm going to remove everything else what if I expend all of this and I say okay actually you have your running total so our first running total is for genes is a running total that runs until right there 14 is one of the running totals that resets because it's a new a new column actually a new category so we start with 14 and then the running total goes until here okay so far so good and then sucks which is great this is getting to where we want to be but there's one thing that we lost right here and we don't want that if you now look at the if we go back to the distorted rows as you can tell we had column types here the data types of these columns were set now after doing all these steps we lose our data types now we only have a few columns right here but imagine you have a table with maybe 50 or 60 of these you don't actually want to get bothered and have to restore all the data types but we can fix this as well okay what do we do we go back and we go to the group row step here we go I'm going to make some spaces here add this to a new line [Music] perfect now what we did previously previously we added a custom column and we applied our function on each of the table objects here that didn't work out but what we can also do is apply our function right away before even doing anything but before going to an add column step um if you look at your table group function there's this little part here each underscore and what that means is that for each of the the products that has been grouped it will generate a table object with the summarize rows so this part here those are the summarized rows you can also think of this as if this underscore represents a table and that's going to be the key for our solution so what you can do is instead of doing only the underscore you're going to edit the code and write FX or running total Open Bracket and you remember the first argument was the name of our running total column I'll call it running total you're ready to comma the second argument within our function was the table we can leave it as an underscore and the third argument is the name of the values column that we want to make the running total on so we call this amount and we close our brackets okay so basically the argument is the same except that we now have this function running against it if I click OK nothing changes but if I now look at the preview of this table here I can click on it and you'll see that the running total was right there okay that's good now the exciting part is see if we still have our data types but you're going to first run into another problem if you open the brackets here you're going to find that it only shows three different columns whereas when we just look at the review we actually saw four different columns here the running total was there but for some reason we're not able to select it right here now the reason for that is borrower query has a good look on what kind of tables are in the column and we have indicated that we have a table that has a column date product and amount and it also indicates the data types so if we want that running total to be able to expand there's one slight adjustment you need to do so our column is called running total so if you wanted to expand as a running total we need to go here you write a comma you write a running total no request just spaces no quotes either just running total and it equals an integer 64. I'll type and then we just close that that's all you do and by adding this little piece of code here all of a sudden you actually have it in the list the running total column appears in the list okay so why did we go through all this pain well our big problem was that our data types were removed so let's say I remove everything except this one here and I now expand let's have a look we get all of our columns but now it includes all of the data types that we need okay so those were a few steps that we took and this easily allows you to run your running total on different groups that you need so that's great but in the same way you can actually run this on multiple groups so now we only took the product called genes but you might also go to a query like over here which has both genes and color and you can perform exactly the same steps so in this case maybe you want to say I'm going to group it by multiple columns I'm going to group it I'm going to have the all rows operation called the titles this creates our function again and then out here again you're going to write your FX running total and now we're going to call it RT and then our values column is called amountsdale that's all we need so this already creates us our RT column and now we just need to add it here and say our RT column is in integer time all right and with this it's going to group it on the level there I'm going to remove everything else expand the columns that we have oh it's a little bit bigger okay and now when you look at the running totals you'll find that for genes in yellow we have a running total that's the first one then for jeans and purple there's another running total so this actually allows you all the flexibility in the world so you want to categorize by a single category two three four five if you want to categorize and do a running total by month you can just have a month or year column and group it actually it doesn't really matter as long as you have created your running total query you're running to the function and you know how to put it into the group by function here you're going to have a performing one you're going to have it on the level that you need and your data types will remain this is a little bit complex I wrote a blog post where I write about everything and you can copy the code there or you can download the example file to work with as well so if you haven't looked on that blog post yet I'll write it in the description below um and if this was any valuable or what is your favorite approach let me know in the comments and I'll see you in the next video okay ciao [Music]

Channel: BI Gorilla

Views: 10,399

Keywords: power query, power query tutorial, power query for beginners, running total, running total power query, cumulative sum power query, create running total power bi, power bi running total, running total by group, running total by category, grouped running total in power query, running total that resets, power bi

Id: ShnWkb6e0jE

Length: 17min 43sec (1063 seconds)

Published: Thu Oct 27 2022