What will the FUTURE bring?! Showing MULITPLE FORECASTS in one chart in Power BI

when you create a forecast you probably don't look into a magical ball and the future values present themselves to you however what's more likely is that you sit together with some team members or we have different departments and everybody gives their version of the forecast so that you then can sit together to agree on a final forecast now it might be very insightful to show all of these different forecasts in a chart and compare them to the actuals and maybe you also want to have the ability to hide all of the forecasts for those months for which you already have the actuals and show them whenever you want to so basically a chart like this one over here and that's what we are going to build together in this video [Applause] welcome to how to power bi my name is boss and if this is the very first time for you visiting this channel then make sure to hit that subscribe button if you want to stay up to date on all of my videos in which i share everything i know about power bi now let's start off by first having a quick look at the charts that we're going to build now here we have the actuals versus the forecast the forecast is the pink line and the actual that's the dark blue line now i want to have buttons that allow me to also add the forecasts from different departments and i want to have a toggle button that allows me to hide those forecasts for those months for which i already have the actuals so that i just have an idea of where i will be at the end of the year now for this last feature we need to combine the actuals with the remaining forecasted values now i have a video that explains into detail how to do this now for this example i'm going to give you the short version as it's going to be our starting point now here we have a chart where i show the actuals that's dark blue line and then the forecast that's the pink line it's nicely connected however that pink line is basically a combination of the actuals and the forecast so if i drag this one up a little bit then you see it continues behind it all right now the important part is the measure so let me open up the measure and here you see that the first variable stores the last sales date so i basically look at what is the very last sales date that i have in my data model so that is the 30th of june here now where it gets interesting is the future forecast variable or the remaining forecast variable where we need a calculate function because we need to modify the filter context that comes from the visual which is over here the year quarter a month now we want to calculate the rev forecast however not over all of the dates but only those dates that are after the last sales date now you see there is also a keep filters function and that one we need because we want to keep still the filters that come from the visual otherwise we would have the same dates everywhere and we would end up with the same future forecast for every single month and then we have the result that should return the actuals for those dates that are before the last sales date and the remaining future forecasts for those days after okay so let's take this now to the next level first i want to add this toggle that allows us to switch between showing the historical forecast and hiding it okay now for this we need a disconnected table so let's go here to modeling and then new table and let's call this one historical forecast and this is going to be equal to and here we can use a table constructor so these curly brackets open close and then let's place them on a new line and here we want to return either show or height okay so now we have here the historical forecast table which is disconnected from any other table in my data model and i'm going to use that value field on a slicer let's add a slicer and place that slicer above a visual just like this and then i take the value field drag it on top of it now here we can either select show or hide now of course with the formatting we can do better so let's make it a little bit prettier let's go to general and put the orientation to horizontal and then here for selection controls turn single select on slice adder we can turn off and then here for the items let's go to outline and put a frame around it and turn the title on now as a title we can show here historical forecast and then put that one in the middle all right let's make it a little bit smaller just like this so now we can either hide or show however of course it's not connected to anything yet so we have to go to a measure to connect it to that slicer so let's go here to the ref actions and future forecast measure and here we have to go to our result variable because we want to have here an if statement because we want to check if the value of the historical forecast is selected now here to check for that we can either create a new measure or we can write over here if the selected value of the value column in the historical forecast table if this one is equal to show then we want to return the total ref forecast final okay and otherwise what we had before the actuals in future forecast and of course we need to close the brackets for a if function and now you see we do show the historical forecast and if i click here and hide now you see we are hiding it however i still need to change the order here on my visual so let's put total factuals to the bottom so that you can see a bit better so height show hide show alright so that part is working now the next thing that we need to do is to add the forecasts from different departments now you might think oh that's pretty easy because here we could just go to dim departments and put departments onto the legends for this visual now let's see how far we get so i go to my visual take dim departments take department put it on the legend and doesn't work now why doesn't it work because we have over here on values total web actions and ref actuals in future forecast okay and if you have multiple values on values then you cannot put anything on the legend so i have to take out total left actuals all right now i could go here to them departments take department and put it on the legend and i still have one line instead of having different forecasts for the different departments now the reason why is because i only took the final forecast now let's go over the scenario i'm gonna go over here to the folder in which i have all of the forecasts and here you see we have one file for each department sales marketing finance we have their forecast for the revenues and spends for the business for previous year and this year okay now there's also the final one as well that is the forecast that everybody agreed on okay now i imported those using power query transformed the data a little bit and got it into this final format where you can see there is a department column and here is basically the identifier that shows us where the data came from from which excel file okay so we have here final finance marketing and sales now let's go back to a measure the rev actual is a future forecast and here you see that i took total ref forecast final only so instead of taking the final one i just take here the total red forecast which is basically just the sum of the forecast column so all of the forecasts summed up which of course doesn't make sense if you leave it as a total but it only makes sense if you filter it by the department now if you go back to the visualization you see we have one line for each department marketing finance and sales and we have a blank now what is the blank blank is the final one because if you have a look at our dem departments table you see we have marketing finance and sales there's no final department in my dim department stable so basically the blank that is the final forecast now you can see that the main functionality is there because we are showing the different forecasts for different departments and we can show or hide the historical forecast however the actuals disappeared because we only have now marketing finance sales and the blank one which is the final one however the actuals is not there anymore and also not when i click here on show you see the actuals is not there so the actuals disappeared because we had to take it out from the value field and also here the final forecast shows up as blank which is not so nice so how can we fix this now one option is to replicate a measure for each department however i don't like it because then you might end up with a lot of different measures and every time there's a new forecast from new department you have to create a new measure so i think that approach is not ideal although it gives you a lot of flexibility another approach would be to add two rows to your dem departments table where you have in the department column final and actuals so that when you break down by department it also shows actuals and the final forecast now you might say i don't like it to add these two rows to my nice dim departments table well as an alternative you can also create a dummy table where you have the forecast source so let's go for that approach so let's go to modeling and then create a new table and this one is going to be my forecast source table now the first table values that we need will come from the dim departments table so let's look for the department name and we also want to have from the departments table the sword id okay which will be also important later on so let's take those two values let's see what this returns and you see we have all of the departments but also a blank value so let's get rid of that one so all no blank row as an alternative you see then we don't have this blank value okay so now we have the first table with all of the departments let's go back to our formula and i'm just going to format this a little bit different so i'm going to put this here all right and now we want to wrap this inside of a union function because we're going to take the union of this table that we just created and now we have to add those two new rows and we can do this by creating a table on the flight so we can use the data table function and here we first have to list the columns that we want to create so for the first column we can say okay this is going to be my forecast source okay now this is going to be a string and then we have the forecast source sort id okay which is going to be an integer now then we have to provide the values so let's open those curly brackets again and then put that on a new row we first are going to have final and we also need a value for the sort id now here we just go very high so let's say 98 we need to have the actuals and here 99 okay let's close the data table function and let's also close the union function so now you see we have the two rows added for the final and the actuals now one thing still annoys me and that is that the column names are department and department sword ld and i actually want to have it the other way around i want to have the forecast source and the forecast sort id instead so i just take over here the table that we constructed and put that one as the first one all right so i just switch them around and now if i go back now you see we have 4k source and focus source id all right so let's now go to data model and connect the forecast source to department okay so that we have a relationship between the two so let's now go to our visual and remove department from the legend now let's now take the forecast source from our forecast source table and put that one onto the legend and now this already looks much better because you see we don't have a blank we have actuals finance finance marketing and sales now here before our last sales transaction you see all of these lines are overlapping but the one that i actually would want to see is the actuals however the actuals is the first one so it shows in the back and you cannot just simply change the order here we need to do that with a sort id column now that's why before i added here that forecast source sort id which is now really important because i want to have the final and the actuals at the end now to change the sorting here you have to select the forecast source go to sort by column and then say forecast source sort id all right now we go back to our visual we see that the actuals is now here at the end and overlaps all of the other ones and what happens if i switch to show well the actuals is gone again all right now it's the easy fix though let's go to a measure now here we need to add another if condition in case somebody wants to see the historical forecast so in case this is true we want to say if and again we need a selected value function and we want to check now the forecast source now if the forecast source is equal to actuals that means we want to show the actuals okay so let's show the actuals and otherwise we want to show the total ref forecast all right now let's close that if function the rest stays as it is and let's see if it works now so i go back to my visual and see that the actuals is not there now of course it looks a little bit messy because there are so many lines so the first thing that we can do to make this a little bit better is to change the formatting so let's go to format and then we can go to data colors and then here for marketing i'm going to take the lighter ones okay so take the lighter colors so that they're less visible and then for the final one i leave that a little bit more visible pink and actual dark blue is fine all right then we can go to shapes and then here i put the stroke width to two and then we go a little bit down to customize series and then here for each series we can format it a little bit differently so for example for the final one we can go for a stalk wave of three so that's a bit more visible and then for the actuals you can also go for three and maybe you also want to have markers now another thing that we could do is that for the different departments let's say for marketing we go for a dashed line and then also for finance we also go for a dashed line and then also for the sales we go for the dash line if you then still say it's way too busy way too many lines then we can add another slicer where you can choose which forecast should show so let's add a slicer and on the slicer we're going to use the forecast source field let's add it to it and here we can make this slice a little bit prettier so let's go to format again then go to general and we want to have horizontal orientation and then we go to selection controls here we want to leave multi select on but maybe without it the control key then i go and turn off the slicer header go to items and here i want to have the frame okay now then let's also add a title and the title over here is going to be a forecast source now we can put the title also in the middle and then let's take that slicer and put it above our visualization so now you can hold your control key select the departments that you want the lines that you want to show and that's it so here is our beautiful graph that lets us show or hide the historical forecasts and lets us hide also the forecast that we don't want to show and this is how you can handle multiple forecasts in one visual now i hope that this video was helpful to you if it was then consider subscribing if you have any questions just post them in the comment section below thank you for watching and i hope to see you in the next video

Channel: How to Power BI

Views: 41,427

Keywords: forecast line, actual line, forecast and actual power bi, forecast and actual in power bi, power bi forecast and actual, multiple forecasts power bi, team forecasting power bi, power bi forecasting, forecasts powerbi, actuals and forecast, dax power bi, power bi forecast vs actual, forecast vs actual in power bi, power bi, power bi desktop, how to power bi, datatraining, forecasting, forecasting in power bi, multiple forecasts, forecasting process, chart in power bi

Id: dWyieDU4zWY

Length: 17min 16sec (1036 seconds)

Published: Wed Sep 08 2021