Allocation Calculations in Power BI | Case Study

in powerbi things get difficult when you're dealing with different granularities of data consider a sales table that is at a transaction level and consider another expenses data that is at the month level now to complicate the problem further what if I tell you that you have to allocate the monthly expenses to daily sales not transactional sales daily sales how do we solve such kind of allocation problems with data at different granularities is something that we will take a look at in this video all right before we move any further it's imperative that we understand that what are we trying to solve here and take a look at the data and understand the problem all right so I'm working in this Excel and I've mocked up the data to kind of show you the data as well as the problem so I have my sales data right here and you can see that the dates are duplicated that means on the 2nd January there were two transactions of the sales now this is my sales data pretty standard columns transaction ID date product ID region units price and sales value now I also have some expenses that I have incurred let's say Ad expenses that I've incurred to carry out these sales operations so if you take a look at my uh ad data you can see that every single month I get one row per channel so in the month of January 2011 I had two rows of data one for Google ads the other one for Facebook ads and this is the total money that we spent in the month of Jan across the two channels now the ask is that we would like to be able to take a look at how much did we spend daily not monthly daily so we need to be able to take this total expense of of the month and allocate it by the proportion of the sales in that particular month whatever sales happen in that month find the ratio of that and allocate this particular amount now that You' understood the problem there are three parts to this particular solution the first part of the solution is that how do we even visualize that how are we going to approach this problem can we just maybe do a rough mockup of how our solution is going to look like that's the logic part of it the next part is going to be once we have mocked up a rough solution and we have visualized that how the end output is going to look like we then start to build our model and lay down our calculations and then even test those calculations and then finally once we have tested the calculations and we have made the model the final part is that how can we present that in a meaningful way that the end user understands the solution really well all right let's just start with Excel to kind of lay foot in understanding the logic as to what are we trying to get at here in parbi so in Excel I have built this simple pivot table here from this particular sales data right here I've built this simple pivot table now what I have done in the pivot table is that I have removed the transactional level granularity and I've gotten to the day level that means I can take a look at daily sales for 2 genan for third genan for fourth genan so on and so forth now if I need to do an allocation then I need to find the percentage allocation that means how much of the ad expense is going to be given to day number one how much of the ad expense is going to be given to the day number two so on and so forth so I need to find that percentage now that particular percentage is going to be nothing but allocation percentage which is going to be nothing but 45 divided by the total of that particular month which is about 1 12200 so that gives me that particular formula D4 divided d25 right here and if I drag this particular formula down I am going to get the total of 100% all of the sales of the month now once I have received the allocation percentage then I need to take a look at what is the amount to be allocated so if I just go real quick on sheet number one take a look at the total of these two values this total is going to be 554 now that is the amount that I need to allocate to these percentages right here so I wrote down 554 across all the cells and then I multiplied 3.8% by 554 giving me the allocated value of $20 on that particular day now this calculation that I have done as a rough work in Excel is going to give me some understanding of what my final output is going to look like in parbi so I'm trying to seek this particular amount which is going to be divided by the day and this is the kind of output that I would want the question is how do we do all of this in parbi let's just jump to parbi and start from there all right in parbi I have loaded all the data let me just kind of show you the data model and what kind of relationships are we trying to build here so on the left we have Barbi and we have the sales table like the way that I showed it to you we also had the products table that's going to be a simple Dimension table right here which is linked through the product ID to the product code right here and we also have built a calendar table which is right here pretty standard nothing that complicated now these are my two Dimension tables and this is my fact table all good to go and I have also loaded my ads table right here now the question is that where do I link this particular date column of the ad in the calendar table now the thing is that if you take a look at this particular visual right here I have my visual which is actually filtering by the Year by the month and by the date all of these three columns are present in the calendar table and using this particular calendar table I will filter two tables the first filter is going to be the sales so obviously I can filter my unit or my sales calculation right here so that filter which is year filter and month filter can come to this particular table that's filter number one and this particular year filter and month filter should also filter my ads table to find out that what was the total money spent in that particular month now what I'm going to do is I'm going to take this particular date now note that this particular date is not a running date this only has month end dates but that's all right so if I just maybe click right here click and build a relationship right here I would now be able to pick up any particular column from the calendar table and filter my sales table and my ads table as well all right let's just start to fire up the calculations and I've built a very simple pivot table here the year and the month is coming from the calendar table date just like the way that we saw it also coming from the calender table against that we have the total sales measure the total sales measure is nothing but a simple multiplication of the units into price which is price multiply by units and every single row of the sales table and that actually gives me my sales calculation right here now the very first first thing if you recall from Excel we need to find the allocation percentage meaning what is the percentage of 45 the day of sales of second genan divided by the month total right here which is going to give me a some percentage and that percentage I'm going to run it down all the cells now we can't really happen to write a formula here and use a divide sign and drag the formula down it doesn't really work like that in Excel so what we need is we need to be able to get this number which is approximately 1,200 across all the cells right here and once I am able to get the 1200 number across all the cells then I can just simply do a divide this by this and that is going to give me the answer the solution for that is going to be something like hey this particular number which is 1 1200 is coming from two filter context the year and the month it's the total of the year and the month and I do not want to slice by the date right here so if I just happen to remove the date filter and write my measure once again that is going to be done so if I just maybe take a look at my monthly total a measure that I have already created I'm saying that I still want to be able to calculate total sales which is this number but all that I would want is please don't apply the date filter so remove filters from the calendar and the date column this filter should not be there if this filter is not there it'll actually pick up this particular number right here so I'm actually going to go ahead and drag this total in my visual and that actually gives me the month total across all the cells once I have received the month total the calculation becomes ridiculously easy all that I would want to do is take 45 and divide it by 1200 and that is going to give me the answer so if you take a look at the ratio calculation that I have built right here which is nothing but total sales this particular value and divided by the 1200 value is going to give me some percentage all that I would want to do is Mark this into a percentage format and we are kind of good to go let's just drag this particular formula in our Visual and that is nothing but my percentage which I also took a look at in Excel at this moment I want you to recall the Excel that we created and in that Excel we had a column for how much money are we spending in that particular month and if you remember that we do have an ads table which is right here ad table in that we can just sum up all the values for the month of January which is 284 + 270 is 554 so let's just go ahead and write a very simple Su function which is nothing but my total ads total ads is going to be the ads table and ads Spence column if I happen to drag that in the visual I am going to get the total ads but the problem is since there was a single date in the stable and that was only 31st of January I get to see the very number 554 against 31st of January now the problem is if the number just appears here how am I going to multiply this particular number with all of these percentages so I need to modify the context of the calculation and I need to present this particular number across all of the values right here simple enough all that I will say is that hey why don't you just not consider the date filter only consider the month filter and the ear filter and the number is going to flow through all of the cells how do we write such a thing I can just go ahead and modify my calculation total ads and I can say something like hey calculate I want to do the same calculation however I would like to remove the filters from the calendar and the date column press enter and drag the formula not drag the formula but just press enter now you can see that I have all of 554 right here now it's incredibly easy for me to take that ratio percentage and multiply that with the ads value and finally I spin out the last calculation which is nothing but the multiplication of the ratio to the total ads that we have created and I drag this particular thing in my formula and that is nothing but the numbers that I'm trying to take a look at at this point if you recall we also had a media column in the ads table which was giving us that what media are we spending the money on so if you just go ahead and take a look at the particular media column this actually shows us that are we spending money on Google ads on Facebook ads on YouTube ads and whatever that is now let's just try to bring that as a filter and let's just see how this work works so if I just go ahead and get the ads right here and I'm just going to maybe drag the media column in right here and convert that let's say to a slicer I'm going to get the ability to slice by that once we have the slicer ready if you happen to click on let's say Google or YouTube or Facebook the problem is that the only thing that is going to change is nothing but the total ads right here because we do not know that how many sales were done through YouTube or how many sales were done through Google ads or on Facebook ads so at the moment it is going to filter the ad value but it's not going to filter my total sales so you would want to consider that in case you have a percentage then this filter actually makes sense but if you do not have a percentage as to how much sales are coming from the three channels this filter does not make any sense so I'm just going to get rid of the filter and let's just move on with our calculation like to give a big shout out about my Dax my power query in the M language courses these are the very kind of Concepts that I talk about in the course following a very stru approach to explain you the logic of the problem so that you understand that how the solution is being built and then you can carry out this solution to implement in in your own data hundreds and hundreds of students have joined my courses and they have benefited a lot in case you're interested I'll be looking forward to seeing you in the course and finally we come to part three of the video as to how do you even start to present the data so that it makes sense for the user to take a look at it now at the moment I have removed all the all the fluff and all the supporting calculations and I'm just presenting two things how much did you spend on a daily basis sorry how much sale did you make on a daily basis and how much did you spend on a daily basis that's all that I'm trying to present right here now at the moment the total sure enough does match up which is 554 money spent across all the channels in that particular month but then the user doesn't get confidence as to this particular number is the right number how do we actually expand on this particular number and show that this number is the right number what we can do is we can carry out some concatenation calculations to show that how this number has been calculated which is going to give the user a lot of confidence all right so here is a very simple breakup calculation that is actually concatenating the numbers that have led to this particular number so the first one is nothing but the ratio we have seen the ratio calculation we've already built it all that I'm doing is using the format function to convert that into a percentage that is one the next one is total ads what was the total ads in that entire month and that is nothing but my total ads calculation formatting that into Dollars now all I'm saying is that if the ratio uh which is this calculation is not blank then please do a visual multiplication so write the ratio which is this one uh use the multiplication or the X sign and then use the total ads number so if I happen to drag this calculation on onto the visual it actually shows you that 3.8 mtip by $554 for the entire month is going to give you this particular number now this doesn't quite make sense when you're taking a look at the data in a matrix visual right here so what you can do is you can convert this into a line chart and it's going to look something like this once the line chart has been formatted so I have total sales right here and I have total ads uh distributed right here which is nothing but my red line now if I happen to hover my line on top of this not only do I get to see the sales of that particular day I also get to see ads which have been distributed on that day and what is the calculation so 12.3% of the sales of the entire month on that day multiply by $537 which is for the month of May is going to give you $66 and that is a very very good way to actually show up that how the allocation was done and what is actually leading up to the add breakup number right here the only thing to note is that this particular calculation which is nothing but my ads uh breakup which is uh the concatenation is just kept it in the tool tip and once you hover the mouse on top of that you get to see the tool tip all right that's been it that's how you kind of handle the sets with different granularities and even carry out allocation calculations in parbi [Music]

Channel: Goodly

Views: 7,662

Keywords: Goodly, Chandeep Chhabra, Power BI Goodly, Power Query Goodly, dax goodly, Data Calculation, Data Allocation, Filter Context in Power BI, Data granularities, Power BI dax calculation

Id: 6OMBCzTM-Zw

Length: 14min 18sec (858 seconds)

Published: Wed May 22 2024