hello everyone and welcome to my channel in my tutorials in this tutorial I will be teaching you how to calculate cumulative spend and also calculate the top n percentage of your customers for example I have three different tables over here this table is showing you the top customers that I have in my data set and these are my bottom customers and these are my table with all customers so the way we identify our top customers and our bottom customers is going to be dynamic using the slider that I have over here let me move this slider to about let's say 90 and you will see that the table that you see over here top customers these are all the customers which contribute to 90 percent of our sales amount and these are the rest of the 10 of the customers who contribute to uh bottom 10 of our sales so this is what I will be teaching you today so let's get started with this tutorial so the first thing that we will do here is we will create a new measure here to calculate our sales value so I'm gonna say total underscore sales is equals to calculate sum of sales amount so this is a very simple measure basically just doing what I have here the sales amount just it's just repeating that I have the sales amount now I'm gonna get rid of the sales column that I added over here and now let's create a new measure here let's call this as cumulative spend is equals to I'm going to say calculate let me bring this to the next line total sales this is the measure that we just created comma I'm going to use the window function over here let me just hide this the first argument within the window function here is from value we need to enter the row value from where it needs to begin I'm going to enter 0 over here comma and then the second argument here is to define whether this is going to be absolute or relative you might be aware in Excel that we use the F4 button to freeze the cells this is exactly what power bi does here but it uses the absolute and relative function over here so in this case I'm going to use the absolute function over here so basically we will freeze the first row of that particular table and then the next argument here is 2 and then I'm going to say 0 but this time this is going to be relative so what is happening here is basically when you are here in this particular row so when I bring in the this particular measure into this table the first value that it gets calculated here is referring to the first row over here which is Adavan customer name it calculates the sales for this particular row and then which is the absolute value and then the second argument here is the 0 again which is relative now both here are going to refer to this particular row which is at 0 and then when we move to the next row over here power bi will now start calculating the value here from the first row until the second row likewise when we move to the third row this is what the relative function is doing right so it is automatically moving to the next row over here when we move to the Third customer over here power bi will now calculate the sales for this row this row and this row and throughout that particular cumulative spend value so that is what this we are doing here with the absolute and relative we are playing around with the values over here and then I'm going to say comma and say add columns we're going to add add a new column over here with all selected with all selected I'm going to use the customer name over here because we have selected the customer name in this particular table I'm going to use all selected customer name I'm going to close the bracket over here for by a comma and now we need to enter the name of the column I'm going to open quotes here and type in cast over here we need to define a different name for this I'll tell you later why we need that let me just move this to the next line over here so that I can format this better for by a comma and here I'm going to call the total sales measure again I'm going to close the bracket now for by a comma and now we need to define the order by customer in this case but this time we're not going to refer to the customer name column that we have in our table but this time we are going to refer to the cost column that we just added in the section above over here that is why we referred to this particular column and with a different name followed by a comma and then we need to Define how this particular column needs to be ordered in this case it needs to be by descending I'm going to use descending over here then I'm going to close the brackets Thrice over here so that we close the calculate function and then commit to this particular piece of tax we've now created the cumulative spend now let's bring in the cumulative spend measure into our table by clicking this box and you will see that let's sort this by descending and you will see that the first row over here has the same amount that we have in our first column however for the second row it is calculating the sum of both these customers and showing us the total value of these two customers and now we need to calculate the cumulative spend percentage for each of these customers so what we will do is we will now create a new measure over here I'm going to call this as cumulative spend percentage is equals to first let's Define a variable over here I'm going to define a new variable I'm going to call this as all sales to calculate the percentage we need the denominator value to be fixed across all of these customers right so to do that what I will do is I'm going to define a new variable called as all sales and then say calculate sum sorry calculate total sales comma and then I'm going to say all of my grocery sales table what the all function does here is basically it removes all the filters that are being applied to that particular data set and Returns the total sales value so and now let me say return all sales in this case and let's commit to this piece of code we've not calculated the percentage yet I'm just going to show you what the value that this is going to show us so as you can see over here it is going to show the same value here of the total for all of our customers now comes the next step now I'm going to say divide divide what divide our cumulative spend the value in this particular cell over here divide that by our numerator value which is all sales which is all sales that we have defined over here our variable that we have defined we've calling that particular variable here and referring to 54 million sales over here and with cumulative spend we are referring to 1 625 sales and when we divide this we are going to get the cumulative spend percentage let's change this to percentage and add two decimal values over here so if we look at this particular column now it is saying that Ravi is contributing to 2.5 percent of total sales likewise if I scroll down to customer Veronica over here so all of these customers are contributing to 21 percent of our total sales and now let's say you want to identify who are your customers who contribute to three percent of your total sales this is where we need to create another measure to do that I'm going to create a new measure now I'm going to call this as filter is equals to if my cumulative spend percentage is less than or equals to let's say 30 percent so I'm gonna do 0.3 then I'm going to return true else I'm going to return false close the bracket here and press enter and let me bring in the filter into this particular section and now you can see that it is defining 30 true and false and I can now filter over here come to filter and show only those rows which are true and then I'm going to click on apply filter and now I can also get rid of my cumulative spend and cumulative spend percentage if you would like to also get rid of filter in this case so these are your top customers who contribute to 30 percent of your total sales now let's say you want to make this Dynamic how can you do this basically you can have a slider here to change the percentage and display the top customers in this case so what I will now do is I'm going to go here to the modeling tab click on new parameter I'm going to click on numeric range and then I'm going to call this as top this is going to be a decimal number starting from 0 up till 1 in this case and the increment here let's say I need increment by 10 each you can play around with this and based on your requirement I'm going to leave the default here at 0.1 and then I'm going to click on create a new slider gets created or a new parameter gets created for me in this case I can move around this and let me just go to the data Tab and change this particular value here to percentage so that the right format appears over here I need to have zero decimals in this case and then let me just make this a little smaller and now I have a slider here to choose the percentage of customers that I want to display in this particular table now I just need to link this slider to our value or the filter measure that we created over here so what I will now do is instead of hard coding the 0.3 percent over here I'm going to change that to Top Value this is the measure or this is the parameter that we created I'm going to refer to that particular Top Value over here and then if it is if it matches then return true else false now I'm going to commit to this particular piece of code and now since the slider is set at 100 you are seeing all the customers that we have in our table contributing to 53 million uh spend or sales in this case and if I bring this down to let's say 30 you will see those customers who contribute to 30 percent of your sales amount if I move this to 50 this will now change to 50 percent of your customer these are your customers who contribute to 50 of sales now let's say you want to take a look at your bottom customers right you want to also know who are your bottom customers who contribute to the bottom 50 so what you can simply do is come back to filter over here and change this to false instead of true and now these are your remaining 50 of the customers so this is how you can create the dynamic top end filter based on the value in your data set so that's it guys in this particular tutorial I hope you found this tutorial helpful you've learned something new today please consider subscribing to my channel for more such tutorials