WHAT IF PARAMETERS in Power BI I Forecasting Power BI Search Trends in 2021

welcome to how to power bi my name is boss i hope that you had a good start into the new year and this is probably a time where you personally but also businesses start to make plans for the new year now something that might help you to do this is what if analysis and inside of power bi there are what-if parameters so that you can go through different scenarios and see what impact it will have now let's have a look how they work in power bi [Applause] if you're new to this channel and you want to learn everything about power behind make sure to hit that subscribe button to stay up to date now let's have a look at the example of today where we're gonna have a look at how to use what-if parameters now in this video we're gonna use what-if parameters to make a forecast of how popular power bi will be at the end of 21. now from this chart you can see that worldwide power bi is catching up with tableau and already over two click now with what-if parameters we can make adjustments to the growth rate for 21 and can say okay what if power bi grows with let's say 54 how will that have an impact and will it then overtake the blow well this depends on how fast tableau will grow so here we have again another slicer for which we use what if parameters the end user can play around with these parameters and select a value that they think is reasonable to see what kind of impact that will have so let's first have a quick look at the data model and the data itself so in the data model you see we have one table with the search interest which comes from google trends which is connected to a date table custom date table and we have a table with already some metrics set up so here we have measures for search interest in power bi click and tableau now let's have also a look at the data itself now here we have one data point for each week starting from 2016 all the way up to the end of 2020 and we have data for different regions worldwide but also us uk germany etc then we have three columns that showed the values for search interest one for power bi one for tableau and one for click i placed these matches on a simple line chart and you see here in yellow we have power bi then in blue we have tableau and in green we have click place a slicer here at the top of my chart where i can switch for example to germany where you can see that power bi is already more popular than tableau now i want to make a forecast for 2021 okay so i have a data table that goes to the end of 21 so you see if i go here to my date hierarchy and then say show items with no data then here in my chart i see now also q1234 for the year 2021 so let's now come up with a simple measure that gives me forecasted value for next year all right so i'm gonna go over here and create a new measure and let's call our measure search interest power bi forecast now to calculate the forecast for next year we can keep it simple and just take the value from one year ago and multiply that with a certain growth percentage all right so here i'm gonna use the calculate function we already have the search interest measure for power bi that we can use and i just want to move one year back so i'm going to use the date add function and here we can use a date column i'm gonna go one interval back and the interval is going to be the year now this i then want to multiply by a certain growth percentage so we can say multiply this by 1 plus let's say 10 percent 0.1 now at the moment we show a forecast not only for 21 but also for all the other years okay so i want to still make an adjustment so that we only show it for 21. so i'm going to go back over here and i'm going to use a variable var forecast okay and then we can say return now only if the year of the maximum date that we have inside of the filter contacts so dim date date now if this one is equal to 2020 or 21 only then we want to return the forecast now you see that gives me the forecast 421 however the lines are not connected so i can go back again and then go back to my if function and say like if it's not near 20 and one then take the search interest in power bi the actual values you see we have a line that nicely connects however then i need to still go to formatting data colors then from here i can make the forecast the same color as the actual values for power bi and then i can go to shapes and then turn customize series on and here we want to customize this series for search interest power bi forecast and i would like to have as a line style the dashed line style so let me do this now for tableau and click exactly in the same way so now that we have this we can insert our parameters and use them to change the growth rate for each software individually okay so let's go over here to modeling where you find the new parameter button all right let's click on it now here we have to come up with a name for the parameters so we can call the first one power bi growth rate percentage okay now this as a data type we can switch to a decimal number and that goes from minus one to a maximum of one and we have increments of let's say one percent now also here you can choose to add the slices straight away to your report which i would do and the default let's first put it at 0.1 10 all right i'm going to click on ok now when you create a what-if parameter a table is created in the background that uses the following function generate series which basically gives you the beginning point minus one goes until one and then the step size of 0.01 and here it gives you all of the values in between now to figure out what is selected on that slicer in your report there is a measure that was created for you power bi growth rate percentage value which uses the function selected value now it returns a value when there's only one value in the specified column otherwise returns a different result so only when there's one value which we have over here then it returns that value and we can use that in our other measures and that's exactly what we're going to do now now before in our forecast we hardcoded the 10 percent now instead of having that hard-coded 10 in there i'm going to make that variable by referring to that selected value in the slicer so we can simply refer to that measure so here we have the power bi growth rate percentage value and now if i make an adjustment here in that slicer you see it adjusts inside of my graph now it still doesn't show as a percentage in this slicer so let's go here to that power bi growth rate percentage and then formula is a percentage with zero decimals okay so let's now also make this slide a little bit prettier okay so i'm gonna drag it up a little bit go here to general and first of all i'm gonna turn responsiveness off okay and i'm gonna adjust the colors a little bit and play around with the formatting so that we can end up with this okay so let's do now again exactly the same for tableau and click as well so i've added now also the what if parameters for the tableau growth rate and the click parameters as well as the sliders i put on this report page so now we can play around with the different growth percentages and see how it interacts with my chart now i do not need the legend anymore in my chart so i'm going to turn off the legend and you also see that for each what if parameter you have a disconnect table and these tables they also show by default in the report view which might not be something you want so i would usually hide them so go here to each table and write that disconnected table now the only problem that i still have with the solution is that we don't have kind of a reference percentage that could be realistic so therefore i'm gonna add three more measures and that are gonna calculate for power bi tableau and click the growth rate that we had last year so that we take that as a reference point i already created this measure before so let me just quickly copy it over and walk you through it so i have one variable search interest from one year ago where i use the calculate function to basically take the search interests of power bi but then not for 21 but from the parallel period which is then 2020. okay so i use the barrel period function there and then i do the same thing for two years ago and then i simply divide one by the other i only want to do that when both the search interest from one year ago and two years ago it's not blank that's it okay so nothing crazy here and then i can repeat this also for click and download as well i can use now these newly created measures in the text box below the slices that we have okay so let's create a text box place it underneath the power bi slicer and here you just simply write last year colon and here i want to have as a value the search interest growth rate from one year ago so search interest growth percentage from one year ago for power bi i'm gonna form it as a percentage with zero decimal places then format it nicely so over here i'm going to put it in the middle then give it a nice background color let's go for yellow and let's give it a little bit of transparency here 50 okay i've added now the text boxes that give me the reference percentage which is basically just the percentage growth rate from last year so that we have just a reference point now as you cannot put directly a filter on the text box which you can do however is insert a slicer and i select it over here 21 and then just go here to format edit the interactions make sure it doesn't interact with anything except the text boxes and then you can hide that slicer like this and in this way you have the text boxes filtered on the year 21. okay let's have a look how power bi is doing relatively to the other two between different regions so over here i switch to worldwide first and i'm going to look at the reference point from last year 18 let's put the slider there as well for tableau the same so i'm gonna go over here to minus one percent and if we click we have minus 27 and you see that power bi is slowly catching up worldwide with tableau now if we look at different regions let's say we switch over here to the united states and we also adjust the slices so that they correspond with the growth rates from last year then power bi might overtake tableau in 21. let's see so then for the other regions for example united kingdom you see that the gap between tableau and power bi is just getting bigger and bigger now if you want to play around with this file yourself the download link is in the description section below as you can see what if parameters are super useful especially if you're doing scenario analysis or making forecasts for next year i thank you again for watching if you still have any questions then make sure to put them in the comment section below and if you have not subscribed yet then make sure to hit that subscribe button and i hope to see you in the next video

Channel: How to Power BI

Views: 41,524

Keywords: what if, what if parameter power bi, what if parameters, what if parameter, what if scenario, what if parameters power bi, power bi what if, power bi what if parameter, power bi what if parameters, what if parameter in power bi, what if parameters in power bi, what-if parameter, what-if parameters, tableu vs power bi, power bi search trends, power bi 2021, power bi interest 2021, power bi, power bi tutorials for beginners, power bi desktop

Id: QXNRseW0R7E

Length: 13min 6sec (786 seconds)

Published: Sat Jan 02 2021