What-if Analysis with Power BI - FREE Masterclass

hello everyone welcome to our october live session on what if scenarios using power bi before we jump into the actual proceedings of the live stream just tell me in the chat if you can hear me loud and clear i could see that we already have quite a few people logged in so i'm assuming there is no technical difficulty getting onto the stream but i just want to make sure that the audio is also clear awesome i can see from your messages that the audio is extremely good and clear we are now live and i have got an exciting session planned for you today this is going to be an epic deep dive into some of the advanced concepts of power bi and uh i'm a little rushed because i i had a coffee emergency um some of you probably know this but recently i celebrated a birthday so my wife got me this fancy pants coffee machine for as a birthday gift and and i'm still learning how to use it so i thought for the livestream today i'll make a coffee from the espresso mission downstairs so i went there and normally we'd make coffee for two people so all the settings were programmed to have like grind the coffee for two people and then do all of this so i just went and i pressed the button and then i sort of noticed that it was just grinding and grinding i'm like no i don't need so much of caffeine because otherwise i'll be jumping around in the stream so by the time i kind of cleaned it up and and and got my coffee it was nearly late like i walked into the room and i saw that oh there's only 50 seconds left hopefully you you are able to get a good drink and enjoy the stream let's see who's here um so this is from moyes he says good morning from southern california chengdu and thanks for responding to my email uh uh hey boys it's always good to hear and um video it says belated birthday vicious thank you venus and there's quite a few of this in the chat window here uh on on the upcoming diwali festival uh diwali if uh for some of you who may not know is is like the biggest uh festival in the indian festival calendar so uh it's coming up in the first week of november and um yeah happy diwali to everybody who is watching the stream live or after the stream is done so thank you so much and um you got some nice hollows from bharat from new jersey and then nick says good evening from cape town then brush my hair as well to fit in without trainer well i didn't brush my hair right there you can see my hair it looks all over the place uh and then this is from a milk car uh greetings from el salvador in central america thank you thank you amilkar for joining um and uh we're already having some questions where is the file the file is already added to the video description on the youtube if you go to youtube and just expand the description of the video you would see there is a link both for the blank data file as well as a completed demo file but we will build the whole thing from scratch if you would want to follow along you can you can definitely get that all right um um and uh i'm just gonna check a few things here and everything looks good uh so we are ready to roll what we will do this is probably um if this is your first time coming here on the stream um what what's going to happen is when we start i'll just do a very quick presentation on what is going to happen in the stream because it's usually about 90 minutes but most of the streams that i've been doing have been going on for two hours so it's a long one so i want to set a good agenda and make sure that you're getting maximum help and information that you are here for so very very short presentation to help us all come to the same page and then we will get into a screen share kind of a presentation where i'll show my screen i'll tell you what's will explain the concept of building what-if parameters and scenario modeling using power bi share a full-length example and then the files are provided in the video description a long way you're more than welcome to ask questions uh you know say say hello messages or things and whatever you want um got some nice messages here tage says chandu garu ela owner which simply means how are you chandu i'm doing great i'm really doing awesome uh i had a patchy sleep last night usually this happens every time i do a stream maybe i'm i'm still nervous or excited for the stream because if i'm recording a video i can do any number of takes until i get the right wording and the right emotion and everything in the stream i don't have choices i i just have um so that's probably a little bit of that i'll try to speak up a bit uh i'm hearing some messages about low sound uh but let's see um and uh wamo tech says hi from somalia i enjoy your videos and it's a pleasure to watch you trying live thank you so much wamu for having coming on to the stream um there's a couple of questions here about is it gonna be recorded um while it it is technically recorded by youtube uh most of the time what happens is after the stream finishes maybe about a week or ten days into the stream streams journey i i make the video unlisted this is just to ensure that you know you are as committed to this as i am and and you all watch together and learn together and then that's the experience so that's and sometimes i take the ideas from the stream and make a smaller video on the channel so yeah technically recorded but i would recommend that you stick around and watch it uh as much as possible all right [Music] let's uh let's start the presentation in fact for the presentation i had the craziest thought i thought i won't do if actual presentation i'll just do like explanation but then when i woke up in the morning today i was like ah that's not a very good idea because there's some things that i need to spell out and it might not be very well done if i if i just talk so i kind of have exactly put together a small presentation just to guide us through rest of the process for now i'll just go through that we are already getting some questions so there's a question from krishna and others um and what i recommend is while you're more than welcome to ask questions please hold off the questions until we get into the topic um so that the questions can be directed within the topic space that we have the topic for today is what if scenario modeling with power bi and this is going to be slightly advanced topic so if you have never done any power bi stuff before you might find certain things a little bit too much like oh what's going on here but hopefully if you have been using power bi for a while then you will find a lot of what i'm presenting today is of extremely high value to you and uh and you know you can just take it from that level there uh all right uh let's just uh add my screen here and then we will so i'm just gonna we got some this message is funny he says please focus on relationship i assume you're talking about like relationship with in the family or are you talking about the relationships between tables and power bi but it felt funny reading that all of a sudden all right you did see a sneak peek of what is it that we're gonna build this is what it looks like uh it's it is kind of like an extra page on top of our existing report so this will give you a scenario modeling of what happens to our profitability and sales and costs and units as we negotiate a better agreement with our customers or nego or our vendors or bring down our costs so that's the kind of modeling that we would be doing but let's just uh quickly um let me present um our agenda for the day what if analysis with power bi uh we're going to be making this this is something that i just showed you um it's a scenario model where we are able to tweak some of the things in our business like we can decide that our price will go up by five percent and then we can see the impact on total sales or total profit or etc uh we could also see what happens if we bring down the cost by five percent or or somehow convince our customers to purchase more chocolates this is all for our awesome chocolates company by the way what what what would happen uh with all of these how all of them together interplay and how our profit can go from 23.7 percent to 34.9 percent so that's uh really what we are doing um the first thing that i will address is what is what if i'll do a quick demo in power bi so that you can understand how this will look what happens when you when you set up a what if model and we'll also talk a little bit about the concept of disconnected tables usually when we are building stuff within power bi or data modeling we are always talking about how things connect to each other but for what if modeling to work you need a disconnected table so how does this work how to bring that in that concept and then we'll just build some calculations and dax we'll build this thing i'll take your questions so that you can if you have any questions a long way you'll be like oh what did we create here or how did we get there i'll help you with that before we begin i just want to say three things about the downloads questions and supers uh there is a blank data file in the video description for this page if you are watching it on youtube just go to the video description expand it and you will be able to see if you have logged on to the stream uh 30 minutes before i actually started the stream like you're waiting there you just open the page and you left and did some other work then you may have to refresh the page otherwise it should be there i've also put a completed demo file for you to get a sample of what is it that we are creating feel free to use that and in the stream i'll build uh the whole thing from scratch anyway and if you have any question please post it in the chat window you're more than welcome to ask questions um please start your questions with queue you can say anything else in the chat window you can say hello you can talk about the stream or your experiences and all of that that is fine but questions put a queue at the beginning and do not repeat your questions it i do get a lot of questions in the live stream especially when i say ask questions people ask a lot so it gets if you keep asking the same question it gets repetitive and i'll have to put you in the bin for 60 seconds and that's that's pretty much it and of course stay on the topic the topic is what if modeling with power bi so we would like i would like to help you maximum on that topic for the day you can ask general power bi questions but i'll try to answer them at the end of the stream if i still have the time and energy and people are keen to hear me at that point uh if you want to support the stream you can send me a super chat or a sticker that's uh again it's not necessary uh what i'm doing i just enjoy doing this so i'll i i do it but if you would like to support that's them now let's start the thing with a dad joke now normally i have dad jokes as part of my presentation but i thought ah we need to take it up to the next level so this time i'm gonna try a grand dad joke are you ready for this okay so there is this grand and he's really old and he needs a cane whenever he needs to go for a walk or something so one day he got up from the bed and then he took out his favorite cane and then noticed that the cane is broken like probably he was putting too much pressure on it when he's walking or whatever so the cane is now broken what do you call this it's called worst cane scenario well enough i'll get into the presentation all right so as i said this is what we are doing here um i'll do a quick demo and then we will go kind of like rewind all the way back to a blank file and then just build it so right now our position looks like this we are making 4.3 million dollars of chocolate sales uh five point uh sorry four point three is our total cost and five point six million dollars of total sales and twenty three percent of profit what happens if i can change my price by let's say ten percent assuming everything else stays the same so we slide this down you can see that immediately our sales would go up to 6.6 million dollars and our profit improves but obviously if you increase the price maybe customers are not happy purchasing same amount so maybe they will purchase three percent less so we'll adjust this our profit comes down likewise because we are charging higher price maybe our partners at the back end they also want a little bit more cost so our costs also go up by they can only go down they can't really go up so our costs remain unchanged our profit will be is here so this is the kind of modeling that we are building where normally a traditional measure is just calculated as of that whereas with what if modeling you ask a question like you know what if we change this how is that going gonna impact so that's the kind of thing that we are gonna do so for now i'm gonna open a blank file which has my model already built in my model as in data model and tables and everything is loaded and here we will demonstrate how how to get started with this kind of a thing i'll do a very simple demo first and then we will build our profitability model from there on although i did say blank file this is actually a file that i used in my previous live stream i did a live stream ever a couple of months ago on how to use power bi so this is the file from there and we will use that let me just have a quick uh read through on the comments to make sure that i'm not missing anything important and then we will go okay all right so we got quite a few hollows and things like that but nothing no questions or no i mean we have not done anything technical so i'm not assuming there will be questions yet um but i'm just gonna address some of the things i think so some every now and then people who come to the stream they'll say either the video quality is not good or there is a lag or something um and this is bound to happen but what uh you could do at your end is when you go to youtube and you're watching the video there should be like a dot dot dot or or a settings icon there and you can use that to adjust your resolution so set it to the higher level possible i'm streaming at 720p so if you set it at that level you should be able to see the stream in a good quality obviously you need a stable good broadband internet connectivity to be able to enjoy the stream but i thought you know i'll just say that out there and this is mad dog hello from lebron is this session recorded um again i did answer this earlier the session is kind of recorded it is on youtube but it will not be there forever online i'll after some time i will take it out and i'll replace it with a smaller video or something like that on the topic if there is enough interest but yeah if you have set aside time to learn please sit and learn this way you will get it all done and any questions you have you can ask them live and i can answer them right away okay so we already have our blank not technically blank a model that is already built that is showing some information okay the concept here is you can use whatever i'm teaching on your existing power bi reports and models so i'm starting with the report that is already there it tells me what is happening to our sales we have already built this from scratch in a previous live stream so if you are completely new to power bi then as i said this session is not really for you but if you've been using you can kind of see that or this is some report there is some tables some values are there now let's understand what is what if modeling so i'll add a blank page here i'll just show you from scratch what it looks like if you go to the insert ribbon sorry we go to the modeling one you will see that there is a new parameter option here you may have noticed this previously or you may never have observed it but this is the gateway into getting into that what if modeling so we'll add a new parameter we'll just see what happens with this so we'll add one for now this parameter we can just put it by default like everything but we'll make it a little bit more objective so we'll say this parameter will give us a choice to tweak our pricing so we we sell a bunch of different chocolates to customers and that's how we make our money and across the board for all of these pro chocolates what if i can increase the price from let's say negative 10 percent to positive 10 percent so we are able to tweak the price imagine you know it's a old style radio and there is a knob that you can adjust to increase the volume or decrease the volume that's what we are doing here the product price can be decreased or increased by 10 percent so we'll call this as price change so that's my name of the parameter again you can name it whatever you want um there is only three styles of data types you can have you can have a whole number a decimal number and a fixed decimal number for the sake of simplicity i'm just going to leave it as whole number and then say minus 10 to 10. what this means is the price can go from negative 10 to positive 10 and when we treat this we'll treat that output as a percentage okay you could also put it into decimal number and then just put negative 0.1 and point positive 0.1 which is same as negative 10 and positive 10 percent but uh i i feel happy dealing with whole numbers um so we'll just do negative 10 moles to 10 increment and default are not really necessary but we'll just put default as zero what this means is by default if nothing is selected that means our price remains unchanged okay again all of this will be fairly confusing the very first time you do it you will not even see the result so you'll be like what am i doing where does this really play so just kind of follow along for now but once we have that on the screen then you will be able to understand it better so we'll click ok we get a a slider here which says price change and a slider naturally this slider will take you from negative 10 to positive 10. so if you keep moving this the value changes okay not only this gets added you will also have a new table introduced into the model this is called price change okay i'm just going to navigate here to my price change model and you can see that this price change table has values from negative 10 to positive 10 one per row for all the values between negative 10 to positive 10. i'm just gonna see if anybody has having any questions ashwin asks can you stream at a 1080p unfortunately no i don't want to do it because it gets uh too cloggy for my internet connectivity and i don't think the there is any extra benefit from 720 to 1080 but uh we'll see um [Music] cannot see the numbers um but there is nothing new here uh um if you are just hearing me say all we are doing is the numbers go from -10 to plus 10. so that's what it is and you can see that in fact you don't even need the what if parameter you can generate this as a table using the table formula generate series negative 10 to positive 10 1 but what what if parameter does is it will kind of package everything in one place so that you have the the data the slider and and the harvesting measures all all created in one go anyhow uh we'll go back here what i'll do is i'll turn off my face so that there is more real estate given to the power bi and then we will go and examine this so we got our price change which is going from negative 10 to positive 10 using a slider okay so you can pick any value through the slider and whatever you are picking you're seeing there but this table also adds a measure for you so it's called price change value power ba generates that that measure as part of the process when you click on the what-if parameter so i'm just going to add a card here to visualize that so this that measure price change value you can see the definition here it is a selected value type of a measure which will tell you what is being selected by by the slider so if i go like this whatever that is it's -5 6 10. again nothing mysterious but you now have a mechanism to change the value as well as you have a mechanism to see what value is picked as a measure okay so given these two things and assuming everything else stays the same in the world we are living in you know we are living in this chocolate selling business we are assuming that there is no other changes only change that we are doing to the system is we are increasing the price by three percent or seven percent what would that do to our overall sales so let's say we are selling 100 worth of chocolate and now i change the price of my chocolate by three percent so the price is three percent more then naturally our sales would be 103 dollars right assuming everything else stays the same so that's how i can use this information to take my current sales and forecast what would be the future sales if this change were to happen okay again this is somewhat naive because in when you do what if modeling you you control a few things but you also assume that there are a lot of other things that cannot be controlled so we will just assume they are not changing but as you get more more and more complicated into these modelings you will be able to build even more complex scenarios where you take input from others in other things that could change and all of that but for the time being we will be very very simplistic so here i got it within my sales table i have a total sales measure so i'm gonna insert that as a a card we'll just see what our total sales is currently we are selling 5.6 million dollars okay so if the price goes up by three percent what would our total sales be that's the question that we want to ask so we can add a new measure this measure we will call it as total sales new okay assuming everything else stays the same then 5.6 would be 5.6 plus 3 percent of it that's what it will be so we can take the current total sales star 1 plus our price change is maintained by the price change value measure so value will be a whole number between minus 10 to plus 10 so it will always be 1 2 3 minus 6 minus 4 like that so whatever that value is we need to divide that with 100 to get it into the percentage rotation and this is how we can take the existing sales and bump it up by three percent so this is my current sales ctrl c ctrl v we'll add a new thing here and here i'm just going to put total sales new and we'll just apply some dollar formatting there so because of rounding we are not able to clearly see so i am just going to adjust some of these things so from 5.618 million we would go to 5.787 million if our price goes up by 3 percent assuming everything else stays the same okay and i can now kind of play with this and then see what happens to my new cell so this is my new cell so i'll just put some background color there so that you can focus on that we'll add a different color there so this is my current that's my new situation and i can see what happens all the way at this end we would have gone up to 6.1 million all the way at that we would have gone down to 5.05 million so venkatesh asks this question how this price change pullout visual has appeared direct from new parameter that's what power bi does when you add a what if parameter once you finish that screen it will automatically add the slider it will automatically set up the price change table and then it will also automatically add the price change value measure to the model so the model has this measure this card is something that i manually added but that slider thing was automatically added by power bm okay so that's the [Music] thing with that all right so we got our total sales new which is a variation of the sales figure increasing or decreasing by price change now this is a very very simple what-if model where we are all we are saying is if the price changes then this is what is going to happen but in real world things can get a little bit more muddy and complicated so let's uh let me draw something for you to help you understand what could happen in the real world i'm just gonna get my sketch pins all right as i said this particular model the one that i'm showing on the screen well right now it's not being shown up but whatever i showed earlier is from a chocolate sales company so i'll just show you the data model for that here there's more tables there but it's basically at a high level this is how it looks like we got sales from product and they they go to the customers now within the product table we maintain the price of each product and in the sales transaction table we know how many units of how many boxes are sold uh and in the product table we know the price per the box okay so the the information like the price change even though we are kind of being very naive with that like if you go it up by 10 then it will go by 10 percent the price change would actually happen in the product table against each product and then it will come here there's a number of boxes bought in each transaction and that needs to take care of that so now let's take it up a new level and then let's imagine what would happen if you if you want to set up a situation where the parameters that you control are not only the price change but also the quantity change so we want to say oh what if the price goes up by five percent and the quantity goes down by two percent what could happen then how would the sales look like in this case we can't directly do a multiplication like the way we have done there because each product is bought a different number of units so it will get into a little bit more complicated nature of calculation but essentially there will be interplay of parameters right if the price goes up and the units goes down then what could happen to my sales what would happen to my profitability all of that we will we will calculate this will give us a chance to play a little bit more with the what if parameters and maybe uh ask answer some of the questions that you might be having like oh what what happened there you'll see all of that clarified one way or another let me read through some of these questions so atul says i think same option in excel as well what if both are same they're kind of same philosophy because excel also has what-if analysis and scenario modeling but power bi at least at one level it kind of greatly simplifies it but it offers a different take on it because within power bi um the within excel if you have a scenario input value then what would happen is you're just putting the input value in a cell and everything else is feeding off that cell to to drive the output calculation so you say price change is negative 10 percent you would put it in the cell or you link it to a form control or something you are putting it there and then it will go and change the values within power bi it is a little bit more uh tricky so here in in power bi what what we are doing is this slider is the input there is no other place in the model where this current value is maintained but that slider is only linked to this page okay so this page it is saying negative 6 i could have another page where i can put a a similar slider i'm just looking for my that's the one so i can put another slider here and then change it and this could be negative three and i can have my cards here for total sales and then total sales new so these numbers react to this slicer setting which is 5.618 and 5.674 whereas here it's this is actually reacting to -6 settings so each page has its own version of the parameter value in a way because the parameter value is only available if there is a slider and it has a value otherwise what would happen is if you take this out and delete then it will default to the default value that you mentioned in the when you create the what ifs and what if thing we said the default is zero so it'll default to zero only when there is a slider available then there is actually a meaning to that so in a way it's similar to excel but also a little bit different and badder says why did you add one to your formula because we want the new sales to be three percent more or six percent less than the previous one so that's why we we only have the value of that's negative six or positive three but we need to go and take that from there so that's where we are all we are doing is total sales times 1 plus this this is how you calculate percentage change in business settings normally so 1 means keep the original value and then plus whatever this value divided by 10 you multiply so you you increase that so if you study that a little bit more closely you will understand exactly what's happening there all right um let's uh now that we kind of got a glimpse of what is happening with the scenarios i will i'll just add one more page i'm gonna change the layout to this so that there is full screen is given to power bi and uh let's go and and and now start thinking about building that profit model so i'll switch back to my old this one this is what we want to create uh we got our information here and if we change these things how will that look like in future so that's what we want to create so there's going to be three parameters our first parameter is price change second parameter is cost change and the third parameter is units change we are changing everything in the business what if the price could go up or down what if the cost could change in either direction and what if the units could change in either direction so let's uh implement that we will add modeling what-if parameter we already have a price change parameter so all we need is two more parameters one for the cost and another for the units so we'll just say cost change and in my demo file i put the cost change like the cost could only go down or it will stay the same this is because in many business settings that is more likely like you will negotiate for the costs to go down if the cost is going up it's not something that you are controlling it's the provider who is controlling it uh you it you you almost never voluntarily go up to your your vendors or your suppliers and then say can i give you three percent more for your product you might say that but you you're not likely to say that that's not something that you decide instead what you as a business person would decide is can i pay you less for the goods that you're selling me because that that will give you more profit right so i thought that's more realistic so we could say cost could go from minimum of zero sorry minimum of negative 10 to maximum of 0. that means cost can only go down but again you can disagree and you can change this to 10 and that will be fine so if you are following along feel free to experiment there so we'll say this and the default is 0 and we will add that as a what if parameter notice that when you add screen will flicker it will add two tables it will add the table cost change and it will also add a slicer for you to adjust that value on this page okay so i can bring down the cost from -10 all the way to keep it unchanged let's add the next parameter as well this one is my units change so we can do with a little bit of marketing and advertisement and offers and whatnot we could increase our sales from minus 10 to plus 10 number of units that customers are buying so by tweaking our marketing settings again this can also be zero now as you are doing this you will realize that [Music] while this is good in many business situations the scenarios or the parameters that you want to model are not always numbers they may be something else like maybe there is a date related parameter that you want to set up or maybe you you want to choose from one of the five or six different text values or something else like that so the built in what if parameter has a limitation that it will only allow you to deal with numbers if you want to do something else that is a bit more complicated then you will have to implement it on your own and bring in to the model the concept is similar you will have to create the tables make a slicer or selection mechanism and a harvester measure but once all of those are there then you will be able to work on it so we'll click okay we'll add this thing here so we got our cost change and units change uh and i'm just gonna copy my price change ctrl c paste it here i don't want to synchronize anything so we got three different controls that we can now tweak i'll just place them in the center of the page like that so i can adjust my price i can adjust my cost and i can adjust my total units so all of these we can tweak so for example what would happen if my price goes up by seven percent and the costs go down by five percent and we are somehow able to still sell two percent more maybe you know we we did a bit of better marketing or whatever so all of this i want to now know what would the final impact on my total sales would be what would be the impact on my cost what would be the impact on my profit units etc and visualize that so that then i can provide provide a before and after kind of a view similar to what we have here in my completed file so this is really our goal we want to create something like this let's [Music] let me see through some of these questions angra says can we have only slider with the parameter can we change it to a drop down with selection you can do whatever you want uh hopefully so if you have a slider and you don't like that kind of bulky slider thing on your visuals you can just click on that little arrow on the corner and change it to drop down and it will be like this and then you can just pick a price change from there i like the slider because it gives you a little bit of that immediate gratification that you're changing and controlling things in some magical way and it it feels more natural for these kind of things roxanne says how can you change a parameter after it has already been created exam example data type was whole number and do you want to change it to decimal um so we will have to try this essentially i i could be wrong but my understanding is when you click on new parameter it is basically a shortcut for the three things that power bi is doing which you can also manually do all the three steps one is it is generating the table the second thing is it is then adding a slider and then the third thing is it is also adding a measure to tell you what is the value of the slider so that's really what is happening um it's it's kind of like think of it like it has a macro behind which is doing all the three things one after another and saving you time whereas you could do all those three things yourself manually as well so if you were to let's say you made a mistake and you want to change uh i'm not quite familiar with the syntax of generate series uh to be honest but let's say we changed our mind we now think our negative 10 to positive 10 is too small the price can go from negative 10 to positive 20. you can put this thing and press enter and it will change you can see that now our price change can go all the way from negative 10 to positive 20. it'll impact every page as well because in reality what it is doing is it's changing the table and adding more rows here so now it goes up all the way to 20. but if you want to add decimal points or something i don't really know the actual syntax of this to to tell you but what you could do is you could kind of create a parameter and learn it yourself see what formula it generates and change the formula from there so that's that is uh what you could do me here says this is brilliant for stem i'm tuning in thank you chendu for this keep doing this often you welcome me here i do live streams on every last friday of every month i may not be doing one in november or even december simply because there's a lot of holidays going on in that time period but in november i might be doing a generic q a type of live stream just to answer your questions on excel and power bi or maybe one of those platforms uh i'll test and see in i'll ask my community what they want and then i'll do it like that in december i'll in all likelihood i won't be doing a live stream because it's too close to christmas and i would like to just spend the time with my family but i'll probably come back next year and i'll be doing live streams in the last friday of the month at least that's what i've been doing all this year and i'll try to do that next year as well i enjoy this it's quite fun talking live and sharing something like this all right there's some other questions there but i'll address them later some people are still asking where is the file or can we have this demo with data um the files are there the in in the video description feel free to download there's a demo and whatever i'm showing is with data only so hopefully that is making sense all right let's uh now that these three things are added on one hand we would like to show what was the before situation so that will go here and on this side of the screen we will show what is the after situation now before information is already there because you can use it for your existing reports and models so in your reports you might already have some measures so whatever is the before that will just go here and whatever visuals you want you can put i'm keeping it simple with card visuals so you can add a card visual and then just show total sales another card visual and then show total cost and then one more card visual and then show total profit in my actual demo file i have got few more things there but because the process is largely similar i'm not going to go and explain every little thing instead we'll focus more on uh explaining the things that are already there in a better way so we got all of these three measures this is my current situation currently we are selling 5.618 million dollars our cost is 4.3 and hence our profit is 1.3 million dollars now what would happen if my price goes up by six percent but units also change by two percent okay and cost goes down by negative five percent this will have impact on all over the thing so some things this more this thing would only impact my cost if i cost goes down by five percent obviously we'll spend five percent less so our cost will only change but changing these two would impact this as well as overall profitability also changes so it gets a little bit more technical and tricky but nothing is hard we just have to figure out the necessary logic and do it so we'll address the easy one first which is add a new measure and then we will calculate total cost new this would be current cost so total cost existing value times 1 plus cost change value divided by 100 same syntax same pattern as earlier so you notice that once you understand the concept you can then easily apply it in multiple situations because the pattern is more or less similar um with with changes where you are doing it by percentage so we can then add a card visual which will tell me what the new cost is going to look like so if you are currently spending 4.3 million and your cost comes down by 5 then your new cost would be 4.07 again nothing super exciting or rocket science there all we are doing is a simple arithmetic multiplication but it will give you uh especially when you are doing this sort of reporting and presenting it to stakeholders and then they are asking oh what if this changes what if that changes this adds a very powerful storytelling dynamic because everything is interactive in power bi you can build it you can send it and they can play with that and they can decide the optimal combination of things that will work for the business so this is a question we know uh is asking and um i didn't actually explicitly say it but uh maybe i should have so i'm gonna address this when we create a new parameter how does power bi know that which field we are actually pointing to and uh in how how is this relationships or maintained this is the key concept i didn't explicitly state it but i did mention it a few times anyway is i'm just gonna zoom down this these are my generated tables okay hopefully you can see see it all there this is my current model the this set of area sales products people and locations this is my existing model these tables price change cost change and unit change are the tables that are introduced by the what if parameter button so when you clicked on that these new three tables were added notice that there is no line connecting the those three tables with anything else in the model that's the concept of disconnected table a disconnected table just exists in the model to support certain things in the output layer so here in the output layer because we had that slider and we would like to be able to select something we need a table so hence we introduced the price change table same with cost change same within units change we didn't deliberately introduce it when you click the button all these things happened automatically but you never connect these tables with rest of the model they just stay disconnected and the only way you can use a disconnected table is by having uh some sort of selection a slicer or a or a slider or some other mechanism like a drill through or whatever and only then it will make sense because otherwise what happens is power bi will see all of the rows in the table so none of your measures can work etc so this is why the the key background concept behind what if parameters is the disconnected table thing again if you are if you know what this is then it's fine but if you are not familiar with none of this terminology it doesn't really mean what they're called they could be called just another table in the model because there is no line we simply call them as disconnected tables and it is not even necessary to use that term to build it because we have already been able to build it without once using that word disconnected table but sometimes when you go to here you will be like oh these things are there what are they doing then you will understand they are not connected with anything else all right um there's some other questions about uh you know things like this where someone is asking can we create a whatsapp group or something like that unfortunately no i'm not going to create any whatsapp group we have our youtube community and i am not able to spend more time answering questions in other channels as well but feel free to post a comment here and i'm glad to help whenever i can okay moving on we got our cost thing cost was fairly straightforward you change the cost down by five percent per product then your total cost is going to come down but when it comes to total sales because now there's two things impacting it we need to think a little bit clear about how to get my total sales so for that we need to go here and understand really how the sales figure itself is generated so i'm gonna look at this column notice that the sales amount is calculated by taking the number of boxes that we are selling so boxes times related product price per box so in the transaction table we don't really know how much money is coming all we know is that we are selling 400 boxes or 600 boxes but then we take that we then follow the thread to the product table to see that oh 400 boxes of almond chocolate each for three dollars so then the total value is 1200 so that's how we are calculating the total sales for that particular row and then we repeat the process and then we calculate the overall total as sum of that column so this is how that is happening now because we are changing two things here we are changing what could happen to the boxes like we're saying i can change how many boxes i'm selling by two percent with marketing so if i do an advertisement offer we are assuming that that will be effective and we will sell three percent more or two percent more and then we are also increasing the price per box so two different things we are changing but they are changing at different places units will change here in the boxes column whereas the price will change here price per box column so two different places this opens up a challenge for us to calculate how new sales will be i'll show you the measure and then if if you have questions i will explain i'll try to explain it anyway but if you have more questions then i'll try to explain it more so we'll write the measure uh this is the first uh i mean i i did i do write these kind of meshes but it's yesterday when i was trying to figure it out it it was fun challenge so total sales new so the total sales new is we kind of need to introduce another sales column here and then basically add it up that's what we are doing this is where you will use an iterator function like sum x which is sum of an operation done on each row in the table so sum x on the sales table for each row within the table we want to evaluate the expression the expression would be take the boxes of the table sales boxes and then first increase the number of boxes by 1 plus units change value divided by 100 because we are saying we could be selling three percent more boxes or 10 percent less boxes so whatever that value is change the change it by that and then once this new number of boxes is calculated this should be multiplied with related related product price price per box which in turn is going up by one plus price change value divided by 100 okay so it's a sum x operation which will basically you won't introduce the calculated column here or anything it all happens in the mind of power bi power pivot and it will calculate the total and then it will just get that final result as this uh if you are not able to dc for this kind of thing in your like oh this looks too complicated uh then you don't need to beat yourself up some x is not something that you can easily understand in the first few months or weeks or sometimes even years of using powerpivot so just take it slow and eventually things will work hopefully we haven't made any mistake uh oh it already exists so we will say this is new too and then let's just see if this is giving you correct result and then i'll explain more ctrl c ctrl v so it says my new sales would be six million dollars if my price goes up by six and units goes by two so i'm just gonna bring everything down to zero i expect the values to be same yeah 61862 so that that makes sense if only this changes then that needs to be about yeah that makes sense so we'll put it five percent and then now you can see the interplay of these two things together changing that so let's just open this measure here and uh let me quickly explain this once again so our total sales is number of boxes times price per box for each row and then total of that now at each row level we could add a column and do it but when you have these kind of parameters and everything you can't really add a column to the table because the parameter value is only available in the context of a page it's not available back in the table so this is something that you need to be doing right here as a measure not as an added column or anything and when you are doing it here the formula that we use is because we want to do an operation for each row on the table and then add it up sum x is the formula that you would use what sum x does is it sums up whatever that x value is for the entire table so x is the expression so it goes to the sales table for each row it will evaluate this expression which means it takes the boxes value of the row multiplies that with one plus percentage change in the boxes which is given by that slider and then once the new boxes are there then it will add the related price per box from the products table and then also increase the price by this percent so it's kind of like a multiplication all the way through but we will have to do it like this so that's the measure let's just see if any questions are there sharik asks sir would you like to explain relationship because sometimes it is confusing now all i can make is a pun on relationship which is relationships are always confusing but because in this session we are not even talking about relationships it is kind of tricky for me to jump into that topic all of a sudden so what i recommend is when you finish the stream just expand the video description there is actually a another video that i did it's exactly like this live stream but uh two months back three months back we did a live stream on how to get started with power bi uh so that would be a good place for you to go and and have a spend some time watching that to understand there is also another video on my channel that talks about understanding various business intelligence and data modeling jargon so that is another place where i explain how star schema works and what is a relationship what are different types of tables and everything in simple terms so definitely check that out i'll add a link to that later on when i when i get some time yeah um niche mastery says by sales boxes you're referring to sold units yeah it is uh it's kind of confusing because i think i i was not careful with these names uh when i was creating them but boxes is is name units but when we are creating our what-if parameter we said units instead of boxes so but they are synonyms number of boxes is number of units uh roxanne says is some x same as excel's sum product um it it kind of is they they are technically similar but i would say it is best to not compare with anything in excel with power pivot or power bi this will save you a lot of time down the stream especially if you are learning power bi uh think of power bi as a completely different thing power pivot and dax as a completely different language that is that kind of looks and feels similar to what excel is because they share the similar syntax of open bracket close bracket and functional style of language but they are different things so approach them differently you might be tempted to think of some product and some extra similar but they they are a different thing altogether uh in in in more ways than than less so if it helps you understand better then maybe yes but i made the same mistake so that's why i'm telling you uh i i have been learning power pivot and dax for almost more around 10 years now so in the first three four years of my dax journey i kept making the same mistake of comparing whatever i'm learning in power pivot and dax with what i already know in excel so then i would i would understand them better for a bit and then i'll come to a place where i'd be like oh wait a sec this makes no sense at all and and that gave me a lot of frustration and i had to go back and forth and relearn a lot of things which made it so much more harder for me both as a a consultant and a person as well as a trainer right because i'm i'm learning and i also teach that's my profession so when i am myself i'm not clear about certain things then i can't explain it to others so now my advice is very simple if you are learning it from scratch and your new learner uh even if you have excel background always try to think of power bi as a different thing that looks similar to excel but approach it differently that will help you learn it faster and better um amit negi um he's please uh you're asking a lot of things but i don't really know what the question is obviously the lessons will not be in hindi because i i don't want to limit it in one language i'll just speak in english i'm more comfortable with that but if you have a question post it uh i think you are just posting some random comments so it will be helpful if you don't uh if you stick to the topic and post a question anyhow we will um we now have created our total sales new measure which is a sum x measure hopefully this is the most complex measure that we are creating today and in the cost as well so now that we know what our new sales would be what our new cost would be i can calculate my new profit so we'll click this total profit new which is nothing but total sales new minus total cost u notice that i'm using new 2 rather than new because the new one only pays attention to fi price change but we have to incorporate unit change as well so i've renamed the i've added a new measure with that and then this would be that we will add it here so you can see that as you tweak this how these numbers change with a little bit of formatting we we can turn that into this so there is literally no difference from here to there all the concepts are similar only thing is i've shown the measures few more things so i got my cost sales profit boxes sold and profit percentage as well which is all fairly straightforward to calculate there is nothing complicated there and then i put some boxes behind different colored boxes and colored the measures accordingly so that it looks a little pretty on the display side of things but and then we can just tweak these things to see what would happen to my profitability so this is where going from that style of things where we are simply showing some information uh we now go into a place where we are we are telling a story we are saying this is my previous state if i change things then this is where we will end up with and we can provide all of that so kim asks when is it better to use a measure versus a calculated field and this is a question that not just kim is asking i got an email from another reader as well and many times i hear this kind of a question when to use a measure when to use a column and and sometimes it it also feels like the there is no clear line like on this side of the line you are in this country on on this side of the line you're on that country there's a lot of blurring going on so here is a simple rule and we will because dax is a complicated language even the simple rules will be eventually broken down the line so you'll come to a place where you'll have to break these rules but we can start with a simple rule and then we'll see where that will take us so you will create a measure if you want to see some sort of a output on the screen because on the screen everything that you see is kind of like a measure excluding text boxes and shapes and stuff like that everything else has to be a measure so i'll open this report here and you'll quickly understand what i mean so in this report all the numbers all the graphs everything is just coming as a result of a measure either explicit or implicit most of them are explicit that means i have written the logic of dax to produce the values but certain things would be implicit whatever may be the case everything that is shown on the screen is a measure excluding the text boxes and the shapes and some icons and stuff like that so if you want to show something on the screen you need to have a mechanism to get it on the screen and that mechanism is measured okay the measure can be written by you or you can leave it to power bi to figure it out in most cases it will be defaulted to some of the values or average or count or unique count or whatever so you can use the default settings or you can customize and you can take a little bit control over this and complicate the matters yourself anything you want to show on the screen it goes here as a measure when would you use a column then well you would add a column to the tables because the columns calculated columns are added to the table they're not added to the screen they're added to the data model table and they're kept there even when you are not displaying anything on the screen so there is a distinction right if you want to show something on screen you use a measure but if you need something on the table all the time for certain purpose then you would add a column okay so this is the simple rule now you can kind of test that and every time you you the test passes do i need this value to be shown on the screen or does it need to go on the table whichever way the test passes you can then decide automatically but there will be situations where the test can be true for both of them in which case what would you do okay so the easy way out in that situations is where you need a value both on the screen as well as in the table for whatever reasons is you will try to do the easiest thing for you as a developer in your early stages of dax okay it gets a little more complicated at the early stages of your journey you will try to do whatever is easy for you if you think of putting it here is easy you'll do it there if you think putting it in a measure is easy you'll do it but as you get more complicated and you start building models that can go into gigabytes and billions of rows and all of that every little decision you make will have a performance of impact so for smaller data sets or simple things experiments it doesn't really matter which way you go everything will be fine but as you get into large scale models and stuff like that all the decisions you make will have considerable impact no matter which way you go you will have hard choices so your choices and everything will need to be more nuanced then you will have to do a bit of research and all of that assuming you are managing such large models you wouldn't be even watching my channel uh you will be uh you will already know all the things that i'm talking about so yeah whatever i'm gonna say is not relevant for you so that's the simple rule i hope that is helpful uh i plan to add a video as well around this topic where i'll simply answer the question when to use a measure when to use a more calculated column so hopefully that will give you that clarity all right let's see what other questions are there um so manoj kumar asks this is an interesting question can we use a target profit to get ideal values change in price or cost so this is where we are going from what if to kind of like a reverse right so we're not asking the question what if my price changes what would the profit be instead what you're asking is what is the input that i need if i want this particular profit now those kind of things again while power power bi might be able to do it's not the place for power bi uh you again you can do it and probably they will add more stuff in visuals and things like that that will make this kind of thing simple um i wouldn't really try to do such modeling in power bi so far my go-to choice for such kind of things is using excel and excel has more complicated suit of applications like you could use solver etc to [Music] um figure out what would be the combination of price change and unit change that will give you a desired profitability because there can be multiple combinations that will give you same profit so you will you will need to set up a model and then do that so normally i would do it in excel while it might be automatically possible to do such a thing in power bi i think the dax required would get way too complicated at least at this stages the other option is and this is probably way better and more fun is you could run a python script or an r script on the data model to answer questions like this because for that kind of thing to happen you need a little bit of programming side of things rather than the dax kind of thing uh to efficiently do it so you could consider using the python visual and then invoking the python script on on the data model uh to to give it an input like i want profit of 50 can you tell me which combination of price and unit change will give me that and then it should be able to do that so suresh asks can we use what if analysis when we use direct query mode this is an interesting question i have not tried what if on the direct query mode by definition what a direct query means is that you are not having the model with you you are subscribing to the model and the model is push it to you and then you just get it like everything that you do on the screen goes back and comes back right so naturally within a direct query the default way is that you cannot add anything more you are just given that model and you will have to use it but what if means that you are introducing a new table because the moment you click on what if you are introducing a new table so if you are using a more a type of direct query either direct query or i don't know if it's called composite or something where you can have the direct query but you can also combine it with your other data then you should be able to technically use what if so i don't really have direct query access or anything because i'm not working in any company i'm just running my own business and i don't really uh like have a warehouse or anything for my this kind of purposes but if you have access to their query give it a try and let me know in the comments if you uh if you are able to add what if models there and if not you know what are the alternatives there that will be interesting to learn okay uh and ashish says sir power query for beginners please share information to learn um we're gonna um because now i think we're getting into a little bit more specific things i'm just gonna open my youtube channel so that i can point to some videos that might be of help to you so there's a lot of videos there so it's tricky for me to if you are looking for a complete beginner to advanced power query tutorial this is the one to go you can see this here on the screen power query tutorial what is it how to use it in four full-length examples this video is about an hour and 24 minutes long so it is a completely in-depth video but it is not a live stream video like this it is a recorded video so there is no interruptions nothing it will be just from the start to end uh just like knowledge transfer so if you are looking to learn power query and this is something that i highly recommend to everybody who is working in the data space in either excel or power bi uh if you have time to learn just one thing then learn power query uh this this will really change your life so watch that video uh even if it means setting aside some time early in the morning late in the evening whatever you do just watch it practice it and you will be fairly decent in power query power query is a very technical language and there's more that you could do but in that video i demonstrate a lot of depth and give you fairly practical ready to use ideas for most everyday things so check that out if you want to learn power pivot just like that then you can go to my past live streams you just have to search it on on youtube and it is called getting started with powerpivot and dax 15 useful examples uh so this one is uh streamed five months ago as i said every month last friday i do a live stream so each month i pick a different topic and the topic few months ago is power pivot in fact i did a similar topic on uh power power query as well last month but i think that video gives better value than this because in live stream there is usually a lot of good content but also people ask questions and there's interruptions and this and that so it's not continuous flow but yeah and if you want to learn power pivot from scratch power bi from scratch then this one is is a good place to go how to make a sales dashboard in one hour uh again the video is two hours long because it's a live stream video but in that video i show people how to build this from a blank power bi workbook this is the reason why i started where that video left off because most people watch the live stream every month so i didn't want to spend time recreating this thing i assumed that this is already there and we went from there so that's that uh legio asks so do you conduct coaching classes for power bi yes i do conduct classes on power bi i'm doing two different things right now um and uh i'll quickly open up my website so i can talk about them as well so the first one is a a self-paced online class it's called power bi play date it is a class where i already built the whole thing and recorded it and you can um you can watch it anytime so this is called power bi i played it if you go to my website or if you search on internet for power bi play it you will come to this page and here you can just read through and if you want you can sign up right now we are in the waiting list mode but if you want to join you can email me and then i'll offer you a back door entry into the program so you can go and get into that so that's probably i played it i'm also doing a three-day live cohort on power bi and this is uh happening in the third week of november so if you are if you have time and if you want to learn face to face well not face to face it's happening online but it's it's like this live stream but fairly interactive and it happens on three days each day for four hours and it'll go on you can also check that program out i'll just so it's a live cohort based power bi training and it is happening in the november's six 16 17 and 18th of november which will be different dates depending on where you live in and we are nearly sold out for this program uh so we already have more than 15 people signed up so and i'm not planning to take that penny so if you want to go for this then you can again book the spot from from that page uh but in all likelihood this might actually close in in a couple more days because we will get into our capacity after that so that's those are the two options and of course there's quite a few free videos and demos and examples on the youtube channel as well all right let's see what other questions uh will doing a what if analysis and power bi impact the original structure of our source data will add a new tables columns no nothing whatever you do in power bi nothing will be added back to your source files uh whether you're doing the what-if analysis or you're adding columns or whatever you're doing everything is built into the model here your source files will remain unchanged they will be just as they are muhammad says i want to start my journey in data analysis i have a plan on my mind but i'm afraid following a track which may take longer or could be a wrong track would you advise um i could all only say all the best because you've not told me exactly what you are planning to do any anything that you do this is not relevant to data analysis this is relevant to general like pretty much everything in life um most things in life unless you experience first-hand and you go through the journey you wouldn't really know like you know being a husband or being a dad or um living in a different country none of these choices no one has all the answers up front so any career choices it's it's like that the thing that we tend to overthink and um and end up not doing at all is um you know we we think if i do this then i don't have the choice to do that or if i do this then all the other roads are blocked for me that's not the case so i'll tell you the example of my carrier i didn't start as a data analyst or or a data analysis person i started working as a software developer but after one year of working i went and did some further studies and then i got back into technology but as a business analyst and after working as a business analyst for some time i i realized that i'm using more more and more excel in my day-to-day life as a business analyst so i thought i'm enjoying this i'm getting so much of fun and value out of this maybe i should talk about that so that's how i jumped into data world so it's it's not something that i i planned and i spent all the time instead i bumped into it and after doing data analysis for some time i then thought you know it will be fun to teach people so that's why i got into teaching so in our lifetime we will have chances to do 10 or 20 things very well if you can only dedicate three to five years for each thing so you're not making the ultimate choice of go down this path and i'll be there for next 30 years instead think of it like this am i able to give it all and and and learn something super valuable and fun for the next three to five years and if so you do it same for my youtube channel i i have my youtube channel for more than 13 years now but for the first 11 12 years i would only post occasional videos and it was always like a secondary thing for me but last year maybe a little before that i i kind of made a mental resolution that i will learn how to use youtube better i will try to become a better youtube person and reach out to the community there so now i am learning how to make better videos and reach out to the community how to do the live streams how to make videos and all of that so if you give time for anything you can master the craft in in a few years time don't expect any quick results but if you are willing to put in that time it will definitely work hopefully that is helpful i don't know sorry if i add new data to existing source file would that auto refresh here yes it will be um maybe i'll i don't have any new data normally i keep like a copy of data as well but what i'll do is i'll open the excel file and we'll try to trigger some refresh and then see what that would do here so here is my actual source data file i put this file as well in the thing and what i'm going to do is so this data goes back so we have data from january to june we could technically add july data but i don't have july information instead what i will do is i'll change some of these things so you can see that the price per box and cost per box are maintained here uh i'm gonna bump up the price of milk bars from 773 to 1290 and then 50 dark bytes from 52 to 14 okay made two changes now naturally this is at a back end level we are not changing the parameter we are changing in the file what the actual input price itself is so this should change my current sales itself as well as my future sales so we'll save this close come here and then if you hit the refresh this will do its thing and that number is 5.8 million dollars uh so it's 5.8 here and 7 million now with with our increased price change so this is what happens if anything changes in your database or your connected systems all of that will trigger here as soon as you either refresh manually or set up an automatic refresh lira an inspiring teacher many thanks thank you so much i really appreciate that and uh all the best to you sanat says i've been following chandu since in 2010 great work thank you sanat uh it's always a pleasure talking to people who have been uh on this journey with me uh for for quite a long time around 2010 is when i kind of really left my job and and got on this journey of working on the website and sharing information with people so it's always pleasure interacting with people who have been here on this path for such a long time all right uh i hope that is helpful this particular stream is a bit shorter than the other ones because we are tackling a specific thing of what if analysis um if you want to stick around and watch i will spend next five minutes showing how to turn this page into that of course this is all fairly cosmetic so there is no um like if you can figure it out then it's fine you don't need to stay back and watch but i thought many of us are not familiar with those things so i'll show you very quickly how i took this and turned it into that that will be good so i'll go the the thing is i wanted to show before change after so usually that's a very good story to tell like most stories will will have a pattern so one pattern that we follow in storytelling is what is it like now what happened and what is the future looking like so that's a that's a classic story so that's where that's how that page is structured so what is it now what we are changing what's going to happen in future because there are three layers three different colors initially i thought we could have three colors but that was looking too busy so i put two colors on both sides and the middle one is a different color so how to get that kind of a color you can insert a shape and a rectangle and just place the rectangle there and adjust it now when you are sizing these rectangles naturally you want to have some sort of a symmetry in the report so the three boxes need to be equally sized so what should be the size of this so this was a bit tricky uh but uh eventually i understood that the page width in power bi the pages don't really have any set width they're all um you know what is called as responsive design so what it means is if i resize my window everything shrinks automatically and if i expand it it will expand it so there is no fixed size for anything on the screen everything is just very fluid but they all follow the 16 by 9 wide screen resolution by default so the default option here is it's 1280 pixels wide 1280 and then 720 uh here so it's basically 720p so if i size this box roughly 425 pixels that will be like one third you can go a little bit more precise so if we set this width to 420 just to be a little short that will be one third of the thing and then i can ctrl c ctrl v so we got three boxes like that the middle box will make it slightly bigger this way because this is the decision box i thought that will need to be bigger so we'll select the first box and last box using ctrl and then change the fill color to a dull color like that we'll select the middle box and we'll make it something like this okay and then you can select all of them turn off the outlines on them so there is no outline and once these boxes are laid out you can select everything using control and from the format ribbon send them to back so at this point they all go behind and you get this nice little three box structure you notice that as soon as you finish doing this all of these cards they were looking all right here but now that they are on the box they have this white color fill in them which was not there earlier but it's always there we never realized because this thing is white as well but the moment you put another color the white color shows through uh if you want you can leave it or you can tweak it as well which is what i did i uh i didn't really like the white color i wanted it to [Music] look a bit different so what i did is i selected all of them again you can hold down the control and multi select and once you select you can go and disable the background so there's no background on them while we are there i'm just gonna change the font family to [Music] this and text size to maybe 32 points so it's not too big so that this looks a bit like that and then depending on what the focus should be here my focus i wanted the focus to be here so i've downloaded the colors on this side so that the current position is faded into the background and only provided as a fyi if we change this is what is going to happen so that's where the focus will be and because the focus is primarily on the profitability i wanted that to shine so we added that color so depending on how you want to narrate you can change that so for example change the data label color uh maybe that so that they they kind of blend in there and then just align them when you are aligning another trick that you can use is you can select all of them and from format align if you align distribute vertically the spacing will be even between them so this is another trick that you could use to line things up and then you can add some text boxes on the top our present sales costs and profits are and then just type that move it place it in the center select everything center align and with a little bit of adjustment you should be able to see that and then again disable the background on that so that this will come up like that so once this is done we repeat the process for this guy and that guy and that's pretty much how you should be able to convert that philip says this is an excellently structured and organized live stream from concept to conclusion well done as always have fun and be awesome thank you philip and it was good to have you on the stream as well i know you come every month so it's good to have few regulars like you show up and cheer for me and this is from akshay been following you from two years learned a lot thanks for being such a nice teacher thank you so much akshay and i wish you all the very best as well uh kim says it's awesome and uh lead you says your website has helped me learn excel better thank you thank you thank you so much for uh learning from me and um yeah that's pretty much it i hope you all enjoyed it feel free to download the completed workbook from the the video description below and then that should give you a a clarity on if if you are not sure exactly how a certain measure is set up or something you know feel free to check the dax there and uh and understand that i'll stick around and see if there is any questions otherwise i'll hang up after a few minutes so let's see what this is bobo asks could you explain about multiple relationship with the duplicate value i like the way of your explanation thank you sir um certainly babu i don't want to do that explanation in this stream so i'll probably add another video on the topic because i think there's a couple of others who asked a similar question uh i will answer that well power bi it used to be the case that the only relationships that you could have in power bi are one to one and one too many but they have added many to many support a while ago even then uh normally in kind of like 99 percent of business reporting situations you never really use many too many relationships uh so it's it's uh it's not something that that is natural or easy to model and and and build some outputs on so you will always use an intermediate table to turn that many too many to many to one or and then one too many so that's how like most of the business reporting i have seen that's pretty much how it is done but i will add a video on this concept and i'll explain that moyes says thank you sir again for a great course and yes i will do regression analysis in excel prior to migrating it into power bi um you welcome boys and i wish you all the very best with that um sanat says can we do some example case on employee performance report sure in fact there's a couple of videos on the channel which talk about similar concept um so feel free to check them out as well uh there's one on top five excel tips for hr analysts which is a live stream that i did three years ago but it is similar to what what i've shown here and uh in the user's power query and in all of that so yeah check that out and yeah thank you so much thanks everyone for all the love and attention and and all the nice messages that you've sent and yeah i hope you enjoyed the stream and i will see you all again in november live stream for november we are not gonna actually do a a topic live stream it's gonna be a q a live stream this is simply because um at the end of november we have thanksgiving day and then at the end of december we have christmas so uh not not that i celebrate thanksgiving but uh um any holiday is an excuse to just chill out and not do work so i'm a bit lazy like that but uh i feel it's it's a good time for for me to also not bother with setting up the stream and all but i will do a q a live stream uh in the number last week so that i can answer your questions and help you understand concepts better philip says lincoln poll idea what do you think of the topic chandual relationships and bob asks could you make one more video on dax studio i would love to i so far i have not really used daq studio much this is simply because i i don't i mean i i work a lot on power bi but i don't really work power bi in a practical situations um more on a teaching situation really so the models and the complexity in teaching situation tends to be a lot smaller than what happens in real life this is because when you're teaching you want to keep things the details simple so that you can focus on concepts and delivery but when you go into an organization and implement power bi that's when you will find having things like dac studio helpful to you because i'm not in that situation uh even when i use black studio it becomes quite academic for me so maybe i will add a video if and when i get a chance to actually use dac studio as part of my client work sometime and then i'll i'll develop more ideas on that all right [Music] ashwin says thank you so much sir uh you're welcome ashwin and um sanat says thanks for the live stream must be too late to bet for you it's actually 6 47 in the morning for me so i i slept i woke up i did the stream and now i go downstairs and relax while rest of the family wakes up and we all have breakfast so that's it someone is asking are you on linkedin yes i am on linkedin but uh um i'm not super active on most of the social media so if you want to catch me or interact with me youtube is a good place i i do post occasionally on twitter so that's another place but yeah if you search on linkedin for chandu you will find our company page and you can like it and follow all right that is all uh friends i hope you all enjoyed the live stream thank you so much for watching and if you are watching and if you celebrate diwali then i wish you all a happy diwali in advance and i'll see you all again in november uh for the live stream and meanwhile i'll see you again on the channels in some other videos or something else thank you and bye

Channel: Chandoo

Views: 9,350

Keywords: power bi tutorial, powerbi, what-if analysis, whatif analysis with powerbi, power bi what if parameters, how to model scenarios in power bi, scenario modeling power bi, power bi, disconnected tables power bi, text what if model power bi, what if parameters, what if analysis dax

Id: jjT_yor0NuA

Length: 108min 42sec (6522 seconds)

Published: Fri Oct 29 2021