Who bought A but not B? . . . (Pattern Analysis in Power BI)

if you do customer buying pattern analysis then you're often going to come across questions like show me the customers who bought the product a but did not buy product b or the other way of thinking about that could be hey show me the viewers who watch the videos on my channel but do not subscribe to the channel H well I got to do something about those people in this video we're going to explore suchar analysis as to how do you find out the customers who are doing one task but not doing the other and there are several nuances that I will discuss in this video so be sure to watch until the end I have loaded some data in Excel and that's the sales table which is the most important part of this analysis we'll take a look at the sales table try to form our question try to structure it and then we'll try to solve the problem of course in Barbi so here is the sales table up on my screen you can take a look that we have a couple of columns right here the customer key the product key the order date the sales amount and this is the vlookup of the name of the product obviously from the products table just to help you understand what does the product look like and if you just Peak down into the names of the products you're going to see that we have sold all of these products I perhaps want to find out that hey why don't you show me the customers who are buying the dissolver for the bike wash but are probably not buying the bike stand something like that how do we do such kind of thing obviously we will try to solve that in Excel then try to to replicate the behavior of that solution in parbi so up on the next screen what I have done is I have built two very simple pivot tables right here in the first pivot table I have applied the first filter that means this is the filter for the product that was sold to all of these customers and then in the second pivot table this is the filter for the second product that was sold to all of these customers now I may want to find out that of the customers all of these people who are buying this product how many of the these customers are not buying the dissolver for that I wrote a very simple kind of a lookup which is hey why don't you just try to find up this particular value in this table is it sold or not if that is sold good to go if that is not sold give me the counts of did not buy and then I just make a count of that how many did not buy are there and the answer is 230 that's exactly what we have to do but we have to obviously translate that inax and data modeling approaches and try to come up with a solution now if you carefully take a look at it obviously we would need the list of the customers this is the list of the customers buying product a and we would need the list of the customers buying product B once we have the list of the customers we then have to do some sort of check that how many customers from this list are there in this list and especially find the customers which are not there in the other list and that is the list of customers that we are trying to seek especially the count of that now let's just jump over to Barbi try to frame this logic in modeling approaches in Dax approaches and try to solve the problem all right let's just take a look at the data model that I'm working with ridiculously simple we have the products Dimension table the calendar Dimension table and the customers Dimension table and these are nothing but my sales data now if I just go over to the visual right here I have created a very simple visualization which is where I have the year and the month and next to that I would like to find out that how many customers are buying let's say one product but not buying the other all right let's just start to create a simple measure I'm going to give it name bot a but not B that's version one and let's just start to write some Dax now here is the Dax that I'm trying to take a look at if you recall from the Excel we had two pivot tables one pivot table was for one set of products the other pivot table was for other set of products and the pivot table was nothing but a table so I'm going to create a table so I'll say something like hey I want to make a table and the table will be the unique list of all the customers so I'll use the values function for that which is going to remove the duplicates and I'm going to maybe use the sales table customer key to remove the duplicates of the sales table customer key column and then I'm going to say that hey uh I would like to apply a filter to this particular table and the filter would be the name of the product so I'm just going to maybe pick up the products table right here and pick up the product name and that is going to be one specific product and I'm just going to close the bracket at the end and that is nothing but my first list of customers these are the list of customers that have bought this particular product now let's just not commited the formula yet I'm going to maybe declare this as a variable so I'm just going to call this as a customers similar to a customers we now going to create B customers so I can nearly replicate this particular measure call this as B customers and just change the name of the product right here from this product to the dissolver product right here and we now have the customers who bought this particular product at the moment both of these um variables are returning a table and we cannot really return a table aable as an output so what we're going to do is we're going to write something like a look up and try to find that how many customers which are under this particular filter are not present in this filter to do that I'm going to use the return function and I'm going to use the accept function for that so I'll say accept and I'm going to say hey uh give me the customers which are there in a list but not there in the b list at the moment the accept function is also going to give us a table or a list of customers which are not present but I don't really want a list because I cannot display that I'm going to rather wrap that around in the count row function close the bracket and then press enter now at the moment if I drag this measure over onto my pivot table I get the answer that means in the month of July there were four customers that bought product a but did not buy product number B and the answer does tally with our Excel and that number is 230 overall one question that is going to come into your mind is going to be obviously if I have to do that on scale on the list of all the products I can't be coming back to my measure and keep changing the names right here can we do something better so that it becomes more Dynamic and definitely more user friendly well let's move on to level number two all right to make the measure more Dynamic what I have done is done some leg work right here and I have bought not bought but built two filters or slices right here which is bought this but not this particular product now these are two disconnected tables to be able to build this I'll quickly show you the model right here so if I just go into the model you can see that I have two tables on the far right I can just pull them back in So product a which is buying this product but not this product now what are these two tables I can just quickly show you that these two tables are nothing but the remove duplicates of the products table and the product name column so remove duplicate of this column and that is right here produces this particular table right here and the same thing is done right here as well that means remove duplicates of the same column and this produces this table right here now because these tables are disconnected they will allow me to select the products in the slicer and not really filter the data all right if you now go ahead and take a look at these two slicers these two slicers are built off the two slicers that we have the two tables that we have now let's just go ahead and start to write our calculation and our calculation is going to be essentially the same calculation that we wrot it a while ago with a little change right here I've just duplicated our calculation in version number two but the only change that I'm going to make is that rather than hardcoding the name of the product right here I will rather pick it up from the slicer which is behind the formula bar and pick this up from the slicer which is also behind the formula bar but how do I do that I'm going to use the selected value function so I'm just going to say hey selected value function so selected value whatever value is selected in the slicer I'm going to go ahead and choose the product uh close that bracket right in here and I'm going to go ahead and delete that and also say selected value and again choose the name of the product so product B and the product column and this is kind of good to go commit on the formula uh rest everything Remains the Same now the user has the ability to go ahead and pick up any particular product the answer Remains the Same 230 but now if the user says Hey I want to take a look at the logo cap and how many people did not buy the dissolver the answer is 2,79 for all of these months of data now at this level this is still very good but then you might as well come around and say that hey if I want to maybe pick up let's say a combination of products let me just say that hey pick up all the customers who bought vests so vest large medium and small but did not buy this particular product now because we have the selected value function used and that is not good at handling multiple values selected in the slicer and hence the answer is a blank can we also handle that in the formula well let's move on to level number three all right we still have to make a slight tweak to the formula but I'd like to show you the output first so if I just go ahead and pick up the first slicer and again pick up the three vests that I was talking about so large medium sorry large medium and small you're going to see that I have picked up three products right here comparing that to one single product right here and I do get an answer what formula does that well the only change that I have done is rather than using the selected value function I have used the values function right here which by the way works with duplicates and scalar values I suggest that you take a look at the video that I have done on the values function if you don't understand that but I all I'm saying is that hey why don't you take a look at the product and those products could be multiple products in the values function and hence I have also use the in keyword to take a look at multiple values being selected and the same thing is right here as well and the rest of the measure follows the same logic and once you've done that you can actually pick up multiple products in this slicer and multiple products in this slicer as well and the numbers are certainly going to change one slight Nuance that I'd like to bring to you before I end this video is that if you take a look at the visual that we have created at the moment you're going to see that the individual rows are not going to match up to the total right here because what we are doing is unique counting of the customer the customer is unique counted in this particular context in this context and the context at the total level could be entirely different because the customer may be present here and here but could be completely unique in the total level as well in case you would like to forcibly match the rows to the total value right here what you can do is you can write this particular measure which is very very simple all that I'm trying to do is I'm trying to form this particular visual so I'll just maybe bring the visual up or maybe copy this particular formula onto the notepad now if you keep these side by side let me help you understand what I'm trying to do is I'm trying to form this particular table so which is the year and the month and that's the table that I have formed using the summ function once I have formed a table what I'm trying to do is I'm trying to do let's say a column creation which is nothing but a not found column creation which is where I'm creating the same two variables a sales and B Sales and then I'm doing an accept of a sales and B Sales now once we have a table like this created within the measure all that I'm trying to do is I'm trying to sum up these values right here and eventually get to the answer using the sumx function now if you don't really understand this technique of forcibly matching the totals I have done a video in the past as to why do the totals go wrong for the non- additive calculations in Barbi and what can you do about it and how do you conceptually think about it if totals have been a big problem to you please go watch that video as well but once you kind of write this formula and you drag the formula onto your visual the totals are going to match and you can take a look at it now that we have the right total which is 555 and the individual rows do match with this number as well all right that's been it finding out the people who have done task a but not task B if you haven't subscribed to the YouTube channel yet please make sure that you follow along and you do subscribe and come back to bucket number a you watch it and you also subscribe it in the end I'd like to give out a big shout out about my courses on Barbi M language Dax data modeling in case you are a beginner and you'd like to master the fundamentals really really well and then take those fundamentals and start to solve your own problems I would highly recommend that you take a look at my courses they are going to be super super awesome and that's been it put down your questions in the comments thanks for watching and I'm going to catch you guys in the next video bye [Music] now

Channel: Goodly

Views: 9,100

Keywords: goodly, chandeep chhabra, Customer Buying Pattern, Power BI Data Analysis

Id: ebRpy1yrSXc

Length: 12min 56sec (776 seconds)

Published: Wed Mar 20 2024