Advanced Scenario Analysis Techniques For Power BI w/DAX

Подписаться 92 тыс.

Просмотров 72 тыс.

50% 1

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.enterprisedna.co/p/ulti...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ulti...
FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-...
FREE - Power BI Resources - enterprisedna.co/power-bi-reso...
Enterprise DNA Membership - enterprisedna.co/membership
Enterprise DNA Online - portal.enterprisedna.co/
Enterprise DNA Events - enterprisedna.co/enterprise-d...
**** Related Links ****
Using Multi-Layered Scenario Analysis in Power BI
blog.enterprisedna.co/layerin...
Scenario Analysis Techniques Using Multiple 'What If' Parameters
blog.enterprisedna.co/scenari...
Advanced Analytics in Power BI: Layering Multiple 'What If' Analysis
blog.enterprisedna.co/layerin...
**** Related Course Modules ****
Scenario Analysis Series
portal.enterprisedna.co/p/scen...
Solving Analytical Scenarios w/Power & DAX
portal.enterprisedna.co/p/solv...
Advanced Data Transformations & Modeling
portal.enterprisedna.co/p/adva...
**** Related Support Forums ****
Advice On Data Modeling
forum.enterprisedna.co/t/advi...
Data Modelling?
forum.enterprisedna.co/t/data...
Multiple Conditions Scenario Analysis
forum.enterprisedna.co/t/mult...
For more data model queries to review see here…..
forum.enterprisedna.co/search...

Наука

Опубликовано:

15 май 2017

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 86

@EnterpriseDNA 4 года назад

***** 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

@jazzista1967 7 лет назад

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

@kh884488 4 года назад

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.

@ajaaskelainen 5 лет назад

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))

@jameshenryhinton 6 лет назад

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.

@mohamedetman1 4 года назад

thank you so much , you know my career path changes to higher level after using Power Bi, DAX ,Power Query. thanks again

@udayg9255 4 года назад

Thank you very much Sam, great example...keep up the good work...

@npmnpm5501 7 лет назад

Amazing. Excelent Explanations!!! Subscribed

@hamayounmughal486 7 лет назад

Great example, keep up the good work. Thanks for sharing

@ranjithkoduvally4613 7 лет назад

Very well explained. Excellent.

@thebartlettsystem1267 4 года назад

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.

@miguelnorena 4 года назад

Great video for learning how to easily create multiple scenarios! Very useful for financial analysis

@nishantkumar9570 5 лет назад

Awesome! Thank you very much.

@waleojo1060 6 лет назад

Sam this is powerful thank you I appreciate

@thomasdutra1 Год назад

Excellent video! Keep up with the good work. +1 sub!

@georgie3891 2 года назад

Thank you for sharing this. This works perfectly!

@EnterpriseDNA 2 года назад

Hi Georgie, glad that you appreciated this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!

@FRANKWHITE1996 Год назад

Thanks for sharing❤

@marcospapis2340 3 года назад

Really, really good! Right on target! Congrats!!!

@EnterpriseDNA 3 года назад

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: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@ImranHussain-xv4se 3 года назад

Great explanation

@excelsior83 7 лет назад

Aaaaamaziiiiinnngggg 👍👍👍

@Claire-ne2ni 3 года назад

Great video! Thanks

@EnterpriseDNA 3 года назад

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: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@fungthongcrootchaiyun4608 3 года назад

That's very awesome. thank you so much

@EnterpriseDNA 3 года назад

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: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@roycemekolle7334 5 лет назад

Very impressive

@luigineri4364 5 лет назад

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%?

@emmanuelamoako9285 7 лет назад

Awesome

@mohammed333suliman 3 года назад

Thanks you, great scenario analysis method using power bi

@EnterpriseDNA 3 года назад

Glad it was helpful! Please dont forget to subscribe to our channel :)

@moc61 7 лет назад

Very impressive demo Sam.

@EnterpriseDNA 7 лет назад

Thanks Martin.

@robbief1 7 лет назад

GR8!

@LiorSternberg 6 лет назад

Great stuff! Can also simulate a drop in quantity due to price change incrise and the affect on gross margin.

@EnterpriseDNA 6 лет назад

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

@phanindrababa6131 5 лет назад

Its nice explanation .. But it is very easy in tableau for implementation

@jdj7453 5 лет назад

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!

@EnterpriseDNA 5 лет назад

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

@RH-nk7eo 4 года назад

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?

@dragontrans 6 лет назад

great

@monikaselicka8710 4 года назад

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!

@EnterpriseDNA 4 года назад

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

@irienoma 3 года назад

How did you "clone" your middle chart with what seemed to be just a click? Thanks! Very useful videos!

@EnterpriseDNA 3 года назад

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 RU-vid 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/ ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-xMI0bMGagf8.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-cW0IelBrOBQ.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE--14Tn1w-D0g.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-1Wd2-bC2xec.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-tsXjeZ2rL0c.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ol-d1T04vvc.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-s2bUGajEfPM.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

@harazinpavel 6 лет назад

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.

@EnterpriseDNA 6 лет назад

Only dates and numbers allow that type of slides

@harazinpavel 6 лет назад

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.

@harazinpavel 6 лет назад

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/

@francesramos16 6 лет назад

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.

@EnterpriseDNA 6 лет назад

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.

@kingsfordgokah6240 Год назад

Thanks Enterprise DNA

@EnterpriseDNA Год назад

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!

@germanshepherd-simba6177 2 года назад

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

@EnterpriseDNA 2 года назад

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 RU-vid 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/ ru-vid.com www.linkedin.com/groups/12004506/

@jenyura 4 года назад

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.

@thomaspham3239 4 года назад

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.

@RH-nk7eo 4 года назад

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.

@wowrb 3 года назад

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 :(

@EnterpriseDNA 3 года назад

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!!! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-ol-d1T04vvc.html portal.enterprisedna.co/courses/enrolled/305950 portal.enterprisedna.co/courses/enrolled/235358

@ExtractingFun 3 года назад

@@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 :)

@bulldogs131 6 лет назад

Really awesome Sam. Is there any way to do different price change for different selection of products?

@EnterpriseDNA 6 лет назад

Yes there certainly would be. Just requires a bit more logic. Maybe for a future video...

@bulldogs131 6 лет назад

Great - look forward to it!

@ExtractingFun 3 года назад

@@EnterpriseDNA Hey Sam, I'm also looking forward to this video. Is there one on the channel?

@3danim8r1 4 года назад

Genius...great video, Thanks for sharing. Could you please provide a source file for same?

@EnterpriseDNA 4 года назад

Thank you Nitin, All pbix files can be found in 'Mini Series' modules at Enterprise DNA Online - portal.enterprisedna.co/

@12a1lqd 5 лет назад

Thank you for the great video. Can I download the sample file in your website? Please teach me the link.

@EnterpriseDNA 5 лет назад

All resources are contained within the Enterprise DNA training portal. Thanks

@borisvazarov5551 7 лет назад

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.

@EnterpriseDNA 7 лет назад

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

@borisvazarov5551 7 лет назад

Thanks. It's working now

@lennywu4859 6 лет назад

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

@EnterpriseDNA 6 лет назад

Nope, it adjusted for what is selected, so I think the name is ok in this case

@rrrprogram8667 7 лет назад

where can i download the smaple file to follow along from the beginning ... By the way .... Good video

@EnterpriseDNA 7 лет назад

All resources can be found at the Enterprise DNA TV link in the description. Thanks

@zm2813 3 года назад

Nice video. But source file is different. No quantity sold, and no date 12/31/2016. Leaves it blank for that date total sales.

@stoneage8810 Год назад

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

@EnterpriseDNA Год назад

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 RU-vid 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 RU-vid 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/ ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/ ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-xMI0bMGagf8.html

@massefromla2554 Год назад

👍🥰

@EnterpriseDNA Год назад

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!

@prasadpm4121 2 года назад

can u plz post the data to work on

@EnterpriseDNA 2 года назад

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/

@mazharhussain8871 2 года назад

Always sales. Show project financing model if u can.

@waynethurman2144 4 года назад

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!