Comparing Actual vs Budget vs Last Year Results in Power BI w/DAX

[Music] hi everyone it's Sam McKai here today I want to show you some really cool analysis uh that you should be able to to create relatively quickly I would say if you just know a few of the techniques that go into it what we're trying to show here is we want to compare our actual results cumulatively we want to see how they're going cumulatively but we want to compare them to budgeted information but also to last year now the reason why I I created this was because I came across a problem where uh a client had created budget at a yearly level but they didn't have any allocation for seasonality for example so they um were I guess incorrectly analyzing information versus verus their their static budget when there was actually some seasonality in the results that would only have really been picked up if you analyzed it versus last year as well and so I thought that it was it was a very relevant example and something um you know and something that in the in the real world you would actually want to do you'd actually want to analyze you know how your results are going versus a a budget you might you may have made up but also you do really want to see how it's tracking versus last year as well at the same time and then what we've done here is that we can then select all our different regions and then for this uh time frame we've got selected we can quickly see okay well where was the Divergence where was the DI Divergence between budget and also the Divergence between actually actual actually last year so as you can see here it looks like you know for this particular example there was uh you know we're underperforming versus budget but we're actually overperforming versus last year um you know so that's quite an interesting interesting in site um and you know some great conversations could be had with that okay so we're going to start from scratch I'm going to show you how you can actually build all of this in in this one video and just a note this will be made available to download so it will be inside the Enterprise DNA training portal uh the details um below uh below the video for that to enroll inside of that okay so let's uh let's start from scratch I've uh created my first measure so total sales this is really simple stuff right so this is going to be our actuals is based on so we're calculating how many sales are we making per day basically okay and then what I also want to do and I want to instead of going through every individual step because I have covered these in other videos is I've pre-created the budgets per day so the budgeting per day um so this is where we have to create some sort of allocation logic where we've got yearly budgets but we want to we we actually want to allocate them per day right and so if I create a new measure and I copy this in we can walk through it and so I've really used variables a lot here and I find these this is a a relatively new feature to powerbi but a seriously awesome one so I highly recommend um understanding how the syntax of this how you can actually write this in your formulas but what I've done here is I've I've basically recreated a number of steps uh and put them into one measure And So It Go step by step we can go okay we work out um this is how you work out the allocation um algorithm for for your budgets but what I've done here is days and date contexts so if this was aligned if this for was aligned to one date then this would just equal to one and then here is days in the particular year and so what we want to do is we want to go 1 divided by say 365 and then times it by the yearly budget and so that's exactly what happens down here we time we divide one by 365 366 and then we times that by the total budget and that's going to give us our budget allocation for one day okay and then we've got a quick uh we've got to calculate the last one we want to do is we want to calculate total sales last year right and so that's not very difficult to do so I want to go I'm going to call this sales ly and then inside of calculate I'm going to put my total sales but then I'm going to go same period last year and then put in dates cool Okay so we've got our core measures set up now right and so if I created a table of all these information this is all of these data points then we we're going to see each individual um result for that particular day but we want it cumulatively right we want it cumulatively so what we're going to do is I'm going to create another measure and I'm going to calculate my cumulative sales now I haven't written it out here I've just I'm showing you what it is because I cover I've covered it a number of other times but all we want to do here is we want to uh and this is a pretty standard pattern you can reuse this over and over again uh we want to calculate our total sales but we want to calculate cumulatively here so what it's doing is it's saying uh for every single date that we land on calculate what the max date is and the max date is always going to be the date and then it will go back and look through for every prior date to that date and then calculate the total Sals and that's how we get the cumulative result as we work through every single date so if I push ENT here uh that's going to give me my cumulative sales and then check out what we need to do to actually get the cumulative budgets and the cumulative sales last year all we need to do is copy and paste this [Music] formula and then sub in a new a new name of the measure and the sales last year push enter and then lastly I'm going to do the same for the budget so I'm going to go cumulative um cumulative budgets here and I'm going to sub in um my budget allocation measure here and now very quickly we've got our three cumulative totals and so if I I start putting these inside a chart with the date so I'm going to put in cumulative sales my cumulative sales last year and also my cumulative budgets and you'll see now that we've got uh these three aligned on one chart uh on one visualization and I'm just going to go grab my city names um and put this into a slicer just get rid of the blank there and you'll see now if I click through all of our different cities we've got this this cumulative total comparison uh visualization which is seriously awesome and now to finish it off what we want to do is we want to create or we want to see what's the deviation what's the variation between um you know our actuals versus our budgets and and our um and our sales last year so I just put this to the back okay so what I'm going to do is I'm I'm going to uh just whip up a new measure I'm going to Branch into a new measure here and I'm going to go actuals uh versus budgets and all I've got to do is grab my cumulative sales and minus my cumulative budgets right and so if we put this into a visualization and new visualization just going to put the date in here as well you'll see that this is now giving us the variation and then I can very quickly recreate this uh for I can recreate this measure um I'm going to go actuals versus uh last year and just find my cumulative sales this year versus cumulative sales last year create a new measure I'm just copying and pasting and then I'll put that into there like so you can actually put them on the same um same visualization if you wanted to um okay and then if we click through now we can see now we can see that not only the cumulatives totals uh together we can actually see what the variation is and see where that deviation occurs and now that I think about it maybe it actually might be quite cool to actually put these on the same chart have another one where we actually see them together right and so I can put my actual last year like that and you can see where the real deviation is on the S I like that I like that a lot and we can really see where that where that difference is now that's some seriously good analysis hopefully you can see that as well and um hopefully you can you find some way to replicate this in in your own analysis so as as mentioned earlier this is available to download so all you got to do is just enroll in U Enterprise DNA's training portal um and you'll be able to see all of the formulas that have gone into um gone into into this example okay good all the best and good luck with this with us um technique

Channel: Enterprise DNA

Views: 68,940

Keywords: Enterprise DNA, Sam McKay, Power BI, DAX, PowerBI, DAX tutorial, Power BI tutorial, Power BI Desktop, DAX Power BI, Power BI Time Intelligence, Power BI DAX, How to learn DAX, Learn Power BI, Power BI training, DAX training, Time Intelligence, Data analysis, Custom visuals, Power BI for beginners, Analytics, Dashboards, Data, DAX for beginners, Advanced DAX, Cumulative totals DAX, Budgetting in Power BI, Budgeting in DAX, DAX tutorial budgetting

Id: MgiOMSs25ZU

Length: 8min 56sec (536 seconds)

Published: Mon Jul 31 2017