Advanced Scenario Analysis Techniques For Power BI w/DAX
Вставка
- Опубліковано 15 жов 2024
- Review how to run simple and advanced scenario analysis techniques in Power BI. In this example we run through how you can dynamic 'shock' pricing and see how that impacts sales
****Video Details****
0:16 - Introduction
2:24 - Total Sales Using SUM vs Total Sales Using SUMX
4:20 - Creating A Supporting Table To Show Price Changes
5:42 - Harvesting The Price Change Percentage Selected
6:56 - Calculating Scenario Sales
7:52 - Showing All Sales Impact Of Selected Products
9:32 - Showing Results Of Selected Products
10:45 - Working Out The Master Calculation
12:37 - Branching Out Into More Measures
12:37 - Branching Out Into More Measures For More Insights
14:44 - Creating Compelling Visualizations To Showcase The Results
16:43 - Conclusion
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterpri...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterpri...
FREE - 60 Page DAX Reference Guide Download - enterprisedna....
FREE - Power BI Resources - enterprisedna.c...
Enterprise DNA Membership - enterprisedna....
Enterprise DNA Online - portal.enterpri...
Enterprise DNA Events - enterprisedna....
**** Related Links ****
Using Multi-Layered Scenario Analysis in Power BI
blog.enterpris...
Scenario Analysis Techniques Using Multiple 'What If' Parameters
blog.enterpris...
Advanced Analytics in Power BI: Layering Multiple 'What If' Analysis
blog.enterpris...
**** Related Course Modules ****
Scenario Analysis Series
portal.enterpri...
Solving Analytical Scenarios w/Power & DAX
portal.enterpri...
Advanced Data Transformations & Modeling
portal.enterpri...
**** Related Support Forums ****
Advice On Data Modeling
forum.enterpri...
Data Modelling?
forum.enterpri...
Multiple Conditions Scenario Analysis
forum.enterpri...
For more data model queries to review see here…..
forum.enterpri...
***** Related Links *****
Using Multi-Layered Scenario Analysis in Power BI
blog.enterprisedna.co/layering-multiple-what-if-scenarios-using-dax-in-power-bi/
Scenario Analysis Techniques Using Multiple 'What If' Parameters
blog.enterprisedna.co/scenario-analysis-techniques-using-multiple-what-if-parameters-advanced-dax/
Advanced Analytics in Power BI: Layering Multiple 'What If' Analysis
blog.enterprisedna.co/layering-multiple-what-if-parameters-for-advanced-analytics-in-power-bi/
***** Related Course Modules *****
Scenario Analysis Series
portal.enterprisedna.co/p/scenario-analysis-series
Solving Analytical Scenarios w/Power & DAX
portal.enterprisedna.co/p/solving-business-scenarios-with-power-bi-and-dax
Advanced Data Transformations & Modeling
portal.enterprisedna.co/p/advanced-data-transformation-and-modeling
***** Related Support Forums *****
Advice On Data Modeling
forum.enterprisedna.co/t/advice-on-data-modeling/4343
Data Modelling?
forum.enterprisedna.co/t/data-modelling/1296
Multiple Conditions Scenario Analysis
forum.enterprisedna.co/t/multiple-conditions-scenario-analysis/5576
For more data model queries to review see here…..
forum.enterprisedna.co/search?q=data%20model
Mr Sam. Great example. I will have to practice the exercise at least 100 times to understand all the dax measures. That was an excellent explanation as well . Thanks for sharing
This is wonderful. So much reporting focuses on the past, but you've given a great example of how a tool like PowerBI can be used to focus on future business outcomes.
Amazing technique - wow! I'm trying to imagine how one could apply different scenarios to different products at the same time both from a DAX point of view and avoiding too much clutter to the viz.
I really loved how you connected the "pricing scenario" with the rest of the data model by using IF( HasNoValue ( price change %) , equal price change %, 0 otherwise)
Also, the calculation of only selected sales: CALCULATE( (all sales), ALLSELECTED ( products))
It took me a little while to get my head around it, but this is fantastic. The place I got stuck was on the concept of All Sales - Selected Sales. What I couldn't see at first was that there is a hidden measure of "Unselected Sales." It is the remainder of what is filtered out. That additional measure would have clarified that "off stage" (off filter) sum (which is the difference of no filters minus filters). I point this out because one of the powerful chess moves here is getting at what isn't on the canvas. If you do work with this approach again, it might help slower guys like me make the jump. Fantastic video, though. Your work is amazing.
thank you so much , you know my career path changes to higher level after using Power Bi, DAX ,Power Query. thanks again
Great video for learning how to easily create multiple scenarios! Very useful for financial analysis
Thank you very much Sam, great example...keep up the good work...
Thank you very much for the video. I have one question: in this video, you showed a range of increase percentage (5%, 10%, etc...). Is there a way, in Power BI, in which the user can input the value say: 23.5%?
Thank you for sharing this. This works perfectly!
Hi Georgie, glad that you appreciated this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!
Very well explained. Excellent.
This is amazing! I have just a question - is it possible to change one product by 5% and another product by 10% at the same time in order to see total changes/increase of sales? Thanks!
Hi, Monika. Yes! it is possible by creating a measure like below. You can expand it as per your requirement.
Total Sales Diff Change =
SUMX(Sales,
IF(RELATED(Products[Product Name]) IN {"Product 1","Product 2"},
[Total Sales] * 2,
[Total Sales] * 5))
Please check Enterprise DNA Forum for this and lot other scenarios - forum.enterprisedna.co/
Regards,
Enterprise DNA
Its nice explanation .. But it is very easy in tableau for implementation
How did you "clone" your middle chart with what seemed to be just a click? Thanks! Very useful videos!
Hello Irena Kostova,
thank you for posting your query onto our channel.
Well, the charts are by default getting interacted with each other due to the "Edit Interactions" option kept "ON" by default between this charts. If you want you can change the interactions between the charts by going through the options available within it.
We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA UA-cam channel pertaining to this topic as well as we also have an in-depth dedicated course available onto our education portal. Below is the link of the courses provided for the reference purpose.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful and meets your requirements that you've been looking for.
Happy Learning!!!
forum.enterprisedna.co/
blog.enterprisedna.co/layering-multiple-what-if-scenarios-using-dax-in-power-bi/
blog.enterprisedna.co/advanced-scenario-analysis-techniques-for-power-bi-wdax/
blog.enterprisedna.co/layering-multiple-what-if-parameters-for-advanced-analytics-in-power-bi/
community.powerbi.com/t5/Community-Blog/Scenario-Analysis-What-Ifs-Tips-amp-Techniques-For-Power-BI/ba-p/559653
blog.enterprisedna.co/scenario-analysis-techniques-using-multiple-what-if-parameters-advanced-dax/
blog.enterprisedna.co/dynamic-scenario-analysis-of-average-order-size-analytics-w-power-bi/
blog.enterprisedna.co/sensitivity-analysis-examples-in-power-bi/
ua-cam.com/video/xMI0bMGagf8/v-deo.html
ua-cam.com/video/cW0IelBrOBQ/v-deo.html
ua-cam.com/video/-14Tn1w-D0g/v-deo.html
ua-cam.com/video/1Wd2-bC2xec/v-deo.html
ua-cam.com/video/tsXjeZ2rL0c/v-deo.html
ua-cam.com/video/ol-d1T04vvc/v-deo.html
ua-cam.com/video/s2bUGajEfPM/v-deo.html
portal.enterprisedna.co/courses/enrolled/235358
portal.enterprisedna.co/courses/enrolled/305950
portal.enterprisedna.co/courses/enrolled/305971
portal.enterprisedna.co/courses/enrolled/236946
What's the difference between VALUES and SELECTEDVALUE here for price change?
I can see you test for HASONEVALUE so would it be better to use SELECTEDVALUE in this case or is there no difference between the two?
Amazing. Excelent Explanations!!! Subscribed
Really, really good! Right on target! Congrats!!!
Hi Marcos Papis, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Excellent video! Keep up with the good work. +1 sub!
Thank you very much for this example - very helpful!
I do have one question while I was trying to use the technique from 9:10 about setting the unchanged [all sales] .
I set my [all sales] using the exact methods from your video. However, my [all sales] kept changing as different products were selected. I wonder which part of my work could be wrong for this?
Thank you for reading my question!
Are your relationships set up correctly? Also double check you have the correct column inside the ALL function. If the correct column is in there you shouldn't have any issues
Thanks you, great scenario analysis method using power bi
Glad it was helpful! Please dont forget to subscribe to our channel :)
Great stuff! Can also simulate a drop in quantity due to price change incrise and the affect on gross margin.
Absolutely you can do this. The opportunities are unlimited around this type of analysis in my view. Check out the scenario analysis on-demand workshop from here to learn more about this - enterprisedna.co/power-bi-resources
Great example, keep up the good work. Thanks for sharing
Thank you for sharing this, i understood the hole concept and tried from side ,
But one thing is that you have created a measure called Scenario sales and in that you have mentioned (1+[pricing scenario]),
Could someone help me in understanding why 1 is been used in that measure.
Thank you in advance
Hello vinod,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post.
Well, the meaning of "1" is that it stands for "100%" so when it's considered like this -
( 1 + [Pricing Scenario] )
It means that when price is 100% and when 5% is selected then price is increased by 5% i.e. 105%.
For furthermore queries, you can also reach out to us onto our Enterprise DNA Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner.
Hoping you find this useful. Also, make sure that you're subscribed to our UA-cam channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ua-cam.com/users/EnterpriseDNA
www.linkedin.com/groups/12004506/
This is great! Can you help me with my issue? So instead of filtering by "product" per your example, mine is "time" and in weeks. Best visual for it is the dropdown filter, except it acts as it should so the rest of my measures aren't projecting the way I want them to.
The time filter should work fine, I'm struggling to understand what could be the real issue here. Should be straight forward to add this. Maybe try a different slicer type and see if that works first.
Very impressive demo Sam.
Thanks Martin.
Hello, thanks for sharing. Could you please advise, how you measure impact of price increase on sales volume, which will definitely happen in real life? As I understand, the scenario should help to find the ideal price which will give maximum profit taking into account the volume decrease vs price increase.
need to use a model or gather data for that, since it involves customer behaviour. Once you have an algorithm that can predict how price increase affects sale volume, its a simple optimisation problem where you can easily find the ideal price.
It would involve either real data (survey) or a theoretical model based on past data.
Then simply create a new table for sales % drop based on price change and then use that as your new scenario sales calculation.
Awesome! Thank you very much.
Really awesome Sam. Is there any way to do different price change for different selection of products?
Yes there certainly would be. Just requires a bit more logic. Maybe for a future video...
Great - look forward to it!
@@EnterpriseDNA Hey Sam, I'm also looking forward to this video. Is there one on the channel?
Thanks for sharing❤
Great explanation
Genius...great video, Thanks for sharing. Could you please provide a source file for same?
Thank you Nitin, All pbix files can be found in 'Mini Series' modules at Enterprise DNA Online - portal.enterprisedna.co/
That's very awesome. thank you so much
Hi fungthong Crootchaiyun, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials.
Here’s the link: ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Nice video. But source file is different. No quantity sold, and no date 12/31/2016. Leaves it blank for that date total sales.
Great video! Thanks
Hi Claire, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link:
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Very helpful. Just downloaded the resources and they are as zip file i personally can't open. Where is the pbix file? Sorry, I'm new to power BI. Thanks in advance.
All resources are contained within the Enterprise DNA training portal. If you are in there and you are downloading zip files then you likely need to first download Power BI desktop to you machine from the Power BI website. Thanks
Thanks. It's working now
Sam this is powerful thank you I appreciate
Very impressive
Helllo Sam, great videos, thank you form this material.
I have one question. Time 5:25, how did you get Slider type Slicer from Price Change column? When I try to insert slicer to my report from numeric type column all I can get is just list or dropdown type of slicer, no option for slider.
I can display slider type slicer just from date type columns. But slider from numeric type columns would be very useful.
Thank you.
Only dates and numbers allow that type of slides
Yes, it works with dates perfectly, but doesn't work with numbers. I create Enter Data table - one column with numbers - doublecheck in Query Editor that column is number type - slicer has only two options - list and dropdown :(. I use September Power BI Desktop release.
OK, problem solved. It is a preview feature and it has to be enabled in PBI Desktop options. powerbi.microsoft.com/en-us/documentation/powerbi-desktop-slicer-numeric-range/
hey, is there a way to do multiple slicers like that? Like if you want to select multiple products and give them different percentages. But in an dynamic way, not embedding each product in a formula.. Like you do in the video, is it possible? I tried but since you need to change the filter context it just works once :(
Hello lenni,
thank you for posting your query onto our channel.
Well, yes you can achieve this type of scenario in Power BI. We're providing a link below where it showcases how this type of scenario can be achieved.
Also onto our Enterprise DNA education portal we've a course created separately which addresses real life scenarios situations and how they can be tackled in Power BI. Please do check our education portal. We're also providing a link below for that.
Hoping you find this useful and meets your requirements that you've been looking for.🙂
Happy Learning!!!
ua-cam.com/video/ol-d1T04vvc/v-deo.html
portal.enterprisedna.co/courses/enrolled/305950
portal.enterprisedna.co/courses/enrolled/235358
@@EnterpriseDNA Good video, however it doesn't explain the original's commenter question - he wants to select multiple products and give them different percentage increases. Not just selecting multiple products. How would we go about doing this? Thanks :)
would be nice if there can be customer or customer / product line specific adjustments solutions... cuz in real life we never see across the board~ high level kind of change
Hello Stoneage8988,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
We're providing a link of the video from our Enterprise DNA UA-cam channel pertaining to the scenario that you're suggesting.
For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well.
Hoping you find this useful! You can subscribe to our UA-cam channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
ua-cam.com/video/xMI0bMGagf8/v-deo.html
Thanks Enterprise DNA
Hi Kingsford, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Great video. 1 question: shouldn't the "Selected Scenario Sales" be really called "All Scenario Sales", as it includes all sales (selected scenario + unselected non-scenario)? Find it a bit confusing. Thanks
Nope, it adjusted for what is selected, so I think the name is ok in this case
Awesome
Thank you for the great video. Can I download the sample file in your website? Please teach me the link.
All resources are contained within the Enterprise DNA training portal. Thanks
where can i download the smaple file to follow along from the beginning ...
By the way .... Good video
All resources can be found at the Enterprise DNA TV link in the description. Thanks
GR8!
great
Aaaaamaziiiiinnngggg 👍👍👍
Always sales. Show project financing model if u can.
can u plz post the data to work on
Hi Prasad, thanks for posting your comment in the video. All pbix file/datasets are available for download in the Enterprise DNA Online, which is accessible via Membership. Find link below. Thanks!
portal.enterprisedna.co/
👍🥰
Hi Masse, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!
Why do people always say Excel blah blah blah....I am a Qlik user n it’s pretty irritating having to listen comparisons about Excel all the time.
I realize you must understand this and may be brilliant, but ripping through this at 90 m.p.h. does not help me learn your technique. I guess it strokes your ego though!