Discover Power BI Desktop's Hidden Scenario Analysis Secrets!

expertiseexcelerate

Discover Power BI Desktop's Hidden Scenario Analysis Secrets!   Take your Power BI skills to the next level with this in-depth tutorial on scenario analysis! In this video, we'll dive into the hidden secrets of Power BI Desktop and show you how to unlock its full potential for data analysis. From creating custom scenarios to advanced data modeling techniques, we'll cover it all. Whether you're a business analyst, data scientist, or Power BI enthusiast, this video is ideal for anyone seeking to enhance their data analysis skills and make more informed business decisions. So, what are you waiting for?  Click play and discover the power of scenario analysis in Power BI Desktop!   • Power BI Hidden Features  • Scenario Analysis in Power BI  • Power BI Tips and Tricks  • Uncover Power BI Secrets  • Power BI Desktop Scenarios  • Advanced Power BI Techniques  • Hidden Gems in Power BI  • Mastering Scenario Analysis  • Power BI Data Visualization Hacks  • Power BI Analysis Tools   #powerbi #powerquery #scenarioanalysispowerbi #BusinessIntelligence #DataScience #DataVisualization #whatifparameterpowerbi #daxpowerbi #powerbiwhatifparameter #timeintelligence #powerbidax #ScenarioAnalysis #DataModeling #BusinessAnalytics #PowerBITutorial #DataAnalysis #Analytics #ScenarioPlanning #VisualizationTechniques #MicrosoftPowerBI

Transcript

00:00Hey friends, welcome back.

00:07In this video, I'd like to introduce you to the scenario analysis in Power BI and how we can play around with this.

00:14And actually, we already have kind of covered it when we talked about ranking and dynamic ranking.

00:20Because what we did, if you remember, if I go back to this one here, we used a slider here.

00:26And this slider was dynamic and allowed the end consumer here to filter here what kind of categories regarding the top categories for the sales figure he or she wants to see.

00:36And something kind of similar to this one with this parameter here can be used in order to create scenario analysis.

00:43So let's do that. Let's go to page number one in here.

00:46That's already quite cluttered. So let's rename this and call it here.

00:50This will be again time intelligence.

00:54And I call it part two, right?

00:57So you get this also as you can download this from the resource section if you want to have a look at that.

01:01But of course, feel free to give this the proper naming you want to name it.

01:05I just want to create a new page so I get a little bit more space for that.

01:09Now, in order to get started, what we need are parameters.

01:13And in our case, we will do it with price and demand, right?

01:18Because these are actually normally two, let's say, factors which might influence our sales.

01:23What I mean by that is normally it's like if a company increases its pricing, then the demand might shrink.

01:30I mean, depending on the pricing power of the company, of course.

01:34So maybe you work at a company and you have a really awesome product.

01:38Then you might increase the price, but the demand remains stable.

01:42Or if the product is that good, maybe the demand goes up because you're currently growing a lot, right?

01:47This could be the case.

01:49Now, let's actually implement this as an example in Power BI.

01:53So let me go inside here, go under modeling, and then let's get started with our first parameter.

01:58It is a numeric parameter, so we use the numeric range here.

02:01And then for this one, well, the name itself, let's call this maybe this will be our pricing.

02:11And it's called actually maybe let's say price adjustment.

02:14That is a better name, right?

02:15Price adjustment.

02:18And then we get started here with a decimal number.

02:22And the minimum should be in this case, let's say minimum is price change minus 0.5.

02:30If we would have to reduce our prices of 50% because we might have an economic crisis.

02:36And that would be the case, right? For a scenario.

02:38Not a very good scenario, but a scenario.

02:40Then the maximum should be, let's just say the maximum is 1.

02:44So now 2 by 1, which would mean that we increase our pricing by 100%.

02:50So we simply double the price.

02:53Let's say these are the extrema.

02:56Then for the increment, of course, then we can't use the whole number.

02:59We should use something like, for instance, always 5%.

03:02So 0.05.

03:04And then the default value here should be 0.

03:07Okay.

03:08So we expect the default value is that nothing has changed regarding the price.

03:12Now that's it actually for our price adjustment.

03:16Let's create this.

03:18And then we have now here our price adjustment slider.

03:21Okay.

03:22And of course, you can see that we can slice and dice this the way we want.

03:26And let's actually type in the number here.

03:28Let's use a 0 here.

03:31Alright.

03:33So maybe one thing.

03:35Let me just go back inside the data view here.

03:38And we see that we have a generate series here from 1 to 8.

03:42Okay.

03:43That's fine.

03:44That's basically our table.

03:45So if I select this, where is it?

03:46Our price adjustment, which is here.

03:48We can see here are the values.

03:50And by the way, let me just select that.

03:52And let's actually change the data type here.

03:54Because you can see I have values like this one here.

03:57And I don't like that.

03:58Let's actually switch this to a fixed decimal number.

04:01Take this option.

04:03Yes, I would like to change the data type.

04:05And now you can see we got only two decimal numbers.

04:07I think that's a better way.

04:08So stick to fixed decimal numbers.

04:10Because then we make sure that we do not get floating values.

04:16Okay.

04:17So we got this.

04:18So next, let's go back to the report view.

04:20And create our next slider in this case.

04:23Which is this time the demand adjustment.

04:25Right?

04:26So if I go in here.

04:27And say I want to create a new parameter.

04:30Go to numeric range again.

04:32This time the parameter name in this case is demand adjustment.

04:37And then we can use in this case again.

04:41A fixed decimal number.

04:42So don't choose the decimal.

04:43Just choose the fixed decimal directly in here.

04:45And then for the minimum.

04:47Let's just say demand reduces minus 0.5.

04:52So 50% less demands.

04:55And the maximum.

04:56Let's say the demand rises 0.5.

04:59So 50%.

05:00Right?

05:01I mean those numbers are really up to your specific use case.

05:04Right?

05:05You might have really experts in your company.

05:07And then you define which kind of scenarios are there.

05:09And then of course you can use your own values here.

05:11Right?

05:12This is just an example which I'm going to use here.

05:13So.

05:14For the increments.

05:15Again.

05:16I'm referring to 0.05%.

05:19And the default here is that the demand remains constant.

05:22So type in a 0 in here.

05:24Okay.

05:25So we got our second parameter.

05:27And also I like to add the slicer.

05:29So let me trade it.

05:30And here we got our second slicer or slider.

05:33Right?

05:34So we have two adjustments here.

05:36So far so good.

05:37Of course you can also format this a little bit.

05:40So.

05:41Here if you click on a formatting option here.

05:44You can adjust the slider header.

05:46Or do you want to show the slider at all.

05:48I can also remove this.

05:49You can also only add a number here.

05:51I'd like to keep it for now.

05:53And if you go to more options.

05:55Like here.

05:56Right?

05:57Then you can also adjust the slicer settings.

05:59For instance single value.

06:00And also do you want to see it rather as a dropdown.

06:03Then of course you could adjust it here like that.

06:06This could be better.

06:07Or what else do you want?

06:09Maybe you could also choose different one.

06:11Like in this case.

06:12Particle list.

06:13A tile.

06:14Between.

06:15Less or equal.

06:16And so on.

06:17In this case single value.

06:18It's the only thing which makes sense.

06:19At least to me.

06:20That's why I can use this one.

06:22But of course you could also go in here and say.

06:24Use a dropdown.

06:25And then here only use single select like that.

06:28Then of course also.

06:29You only have the option to select like that.

06:31Right?

06:32It depends on what you prefer.

06:34Regarding the visualization.

06:35In this case.

06:36Let me stick to that.

06:37So let's go here also.

06:38For this one here.

06:39And choose a dropdown.

06:41And then also use a single select.

06:43So I can only select one scenario.

06:44Right?

06:45Like that.

06:46And of course you can play around with the values.

06:48You can make them bold.

06:49For instance also as well.

06:50So you see bold values in here.

06:52It's really up to you how you want to style that.

06:54Right?

06:55For instance of me.

06:56I want to go here.

06:57And let me uncheck this here.

06:58And keep it the default one.

06:59Okay.

07:00So we got our two parameters here.

07:02Now of course we need to visualize something.

07:04So let's say in here.

07:06We would like to see the sales development over time.

07:10So let's go with our dates table here.

07:12Let me check that.

07:13Click on it.

07:14And then we switch this to a bar chart.

07:17And then we need to have our KPI.

07:20Which in my case is total sales.

07:22Because I'm obsessed with sales.

07:24So you can see that.

07:25That's why I only use the same measure all the time.

07:27So here's our table.

07:30And of course we could also if you select it.

07:32Drill into it.

07:33Let's visualize that on a quarterly level.

07:36And then let's also go on here.

07:38And click on this one here.

07:40And maybe add the data labels.

07:42Okay.

07:43And by the way you know.

07:45I just want to mention that.

07:46Which is because it's new in Power BI.

07:48You can also double click here.

07:49Right.

07:50And then if you see the blue border.

07:52It can also go on here for data labels.

07:54And do the formatting like that.

07:55Right.

07:56Right click here.

07:57And for instance.

07:58Format labels.

07:59Or right click here.

08:00And do the filling here.

08:01And so on.

08:02So.

08:03Click outside.

08:04And now we got our normal visual back.

08:06Alright.

08:07So we got here our visual.

08:09And here we see the development over time.

08:12Now let's actually use those scenarios.

08:15Or these parameters for our scenario.

08:17And apply it to a formula.

08:18To see what.

08:19How the sales might change.

08:21Depending on what our experts forecast.

08:24To do this.

08:25We can actually create a new measure in here.

08:27Let's right click on the measures table.

08:29Click on new measure.

08:30And then let's actually zoom in.

08:32That's the measure.

08:33And call this maybe total.

08:35And I call it scenario sales.

08:37Okay.

08:38Scenario sales.

08:39I say equal.

08:40And I go to new line.

08:42And then I'm doing something kind of similar.

08:44To our total sales.

08:45Which was just the sum x function.

08:46Right.

08:47Remember.

08:48For total sales.

08:49We use sum x.

08:50Over the order table.

08:52And we simply use the.

08:54In this case.

08:55Was the selling price.

08:56Selling price.

08:57Multiplied with the amount.

08:58Right.

08:59Amount.

09:00That was our original.

09:01Total sales figure number.

09:03What we'd like to do now.

09:05Is the following.

09:06We use.

09:07Instead of.

09:08The selling price itself.

09:09We multiply the selling price.

09:11Times one.

09:12Plus.

09:13Our price change.

09:14Right.

09:15So our.

09:16In this case.

09:17How did we name it.

09:18In this case.

09:19Price adjustment.

09:20So one.

09:21Plus.

09:22The price adjustment.

09:23Price.

09:24Adjustment.

09:25And the value.

09:26Like that.

09:28And then we also multiply this.

09:30So we go further.

09:31Times the amount.

09:32Amount.

09:33Amount.

09:34Amount.

09:35And the amount.

09:36Is also changing.

09:37Depending on.

09:38The demand change.

09:39Right.

09:40So in this case.

09:41How did I name it.

09:42Demand adjustment.

09:43So.

09:44One.

09:45Plus.

09:46The demand adjustment.

09:47This one.

09:48Here.

09:49Close it.

09:50And also close the sum x function.

09:52And.

09:53As you already know.

09:54I then.

09:55Select everything.

09:56Control C to copy it.

09:57I go to my.

09:58Fiddle.

09:59Helper here.

10:00For formatting.

10:01Go to edit.

10:02And I paste it inside.

10:03Let it do its magic.

10:05Just wait a little bit.

10:06And now I copy this one here.

10:08Go back to my Power BI report.

10:10And replace everything I have in here.

10:12With my new formula.

10:13All right.

10:14Here we go.

10:15So let's press the checkbox here.

10:17All right.

10:19And then we have our new.

10:21Calculation here.

10:22So that's our measure.

10:23And let me just select the measure first.

10:26First.

10:27Let's go to the dollar symbol here.

10:29And also make sure we only show.

10:31Two decimal numbers in here.

10:33And then we can simply add this also to our chart.

10:36So I can go in here.

10:37I can either click on an object here.

10:39And go to add data.

10:41Go to my measures table here.

10:43And total scenario sales.

10:44Or I can also drag and drop it directly here.

10:46So let me take this option here.

10:49And now you can see.

10:50Currently we have different numbers from here.

10:52Because we currently have.

10:53Selected something different here.

10:55So let's switch back to zero.

10:57In here.

10:58And also switch back to zero.

11:00In here.

11:01And you can see that we got exactly the same values.

11:03Right.

11:04So this is the default scenario.

11:05There's no price adjustment.

11:07And there's no demand adjustment.

11:09And now we can.

11:10Or the end consumer.

11:11Now can choose.

11:12Okay.

11:13Depending on various scenarios.

11:14Which you might.

11:15Well.

11:16Develop.

11:17In your company.

11:18So internally.

11:19You can now adjust the price.

11:20Let's say.

11:21You say.

11:22Well.

11:23Our product is that good.

11:24And the demand is high.

11:25So we can adjust the price.

11:26Maybe.

11:27Let's say.

11:2820% more.

11:29Click on this.

11:3020% more here.

11:31And you see that.

11:32This would mean that.

11:33For instance.

11:34In Q4.

11:35We would have made.

11:36280,000.

11:37Instead of.

11:38182,000.

11:39Right.

11:40So.

11:41Probably.

11:42Better.

11:43Of course.

11:44The price.

11:45But you might also say.

11:46Well.

11:47We want to adjust the price.

11:48Even further.

11:49We would like to see.

11:50We increase our prices.

11:5135%.

11:52For instance.

11:53This is even better.

11:54But let's just say.

11:55If we do this.

11:56Then we expect.

11:57That the demand.

11:58Will.

11:59Also.

12:00Decrease a little bit.

12:01Because some people think.

12:02It's too expensive.

12:03And let's say.

12:04The consumer.

12:05Well.

12:06The demand will.

12:07Decrease 15%.

12:08So we say.

12:09Minus 15 here.

12:10And select this one here.

12:11And we see that now.

12:12Of course.

12:13The value here.

12:14Gets reduced a little bit.

12:15Right.

12:16Because we have.

12:17A higher price.

12:18But also.

12:19The demand is lower.

12:20But still.

12:21With this scenario.

12:22We still make more money.

12:23Right.

12:24Because we can clearly see.

12:25That the scenario sales here.

12:26The dark blue one.

12:27Is higher.

12:28Than the original.

12:29Values we had.

12:30And.

12:31This is it actually.

12:32For an introduction.

12:33To scenario analysis.

12:34I mean.

12:35I totally get.

12:36That this is.

12:37Of course.

12:38Is only.

12:39The beginning.

12:40That's really the basis.

12:41But of course.

12:42If you have.

12:43These use cases.

12:44In your organization.

12:45And you want to.

12:46Well.

12:47Show them.

12:48And use them.

12:49In Power BI.

12:50Then this is the way to go.

12:51So you can really.

12:52Add.

12:53Various.

12:54In this case.

12:55Parameters.

12:56In this case.

12:57We have two.

12:58Variables.

12:59Which influence that.

13:00And.

13:01Then of course.

13:02You need to.

13:03Talk to specific.

13:04People in the organization.

13:05Which do these.

13:06Forecasting.

13:07And then you can.

13:08Get their opinion.

13:09And then of course.

13:10You can use.

13:11What they expect.

13:12To happen.

13:13Inside your reports.

13:14And this can be given.

13:15To the end consumer.

13:16And then he or she.

13:17Can then adjust.

13:18Various.

13:19Options here.

13:20Or I can play around.

13:21With these options.

13:22To figure out.

13:23What this means.

13:24For sales figures.

13:25Right.

13:26So that's it.

13:27Hopefully that was helpful.

13:28Try this out yourself.

13:29If you have questions.

13:30Of course.

13:31Feel free to ask.

13:32Otherwise.

13:33Thanks for watching.

13:34And I can't wait to see you.

13:35In the next video.

13:36Until then.

13:37Best guys.

Category

Transcript

Recommended