Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships, Data Modeling & Much More!

[@PunmasterSTP]

Power Pivot, DAX is lit, Just add some data, And that is it! Build a model, No need to throttle, Your imagination And productivity. All you need is a key, To Power BI, Publish your work, If you don't it's a cry -Ing shame, Don't do things the same, Way. Use your skills, Put on the bells And whistles will blow. Now it's time to go... To the next video. 😎

[@excelisfun]

Bam!!! Rocking Poetry to inspire all who read!! Power Pivot, Power BI and DAX, all fill the need!!! Pun!!!! A great Teammate!! Rocking it with Data Analysis checkmate!!!

[@PunmasterSTP]

@@excelisfun Thank you for those very kind words, and dang, they were in some fresh verses!

[@dimmak8206]

barsss

[@KevinStudent]

Based on my personal experience, you only have about 100 actual viewers, but we all watch each video 5,000 times. Cannot thank you enough for the incredible work and help you’ve given to so many people (which is obviously way more than just 100)!

[@excelisfun]

Thanks for your kind words : ) Always glad to help!

[@excelisfun]

LOL in the last month there was over 1/2 million unique viewers

[@guyelena]

Thanks!

[@excelisfun]

Thank you very much, Elena!!!! Your donation helps me to keep making videos : ) : ) : ) : )

[@sane7263]

Most of the people on youtube make long videos and then just keep talking. But you Mike...you are always in action!! you never disappointed. Thanks Mike!

[@excelisfun]

Yah, telling a good story is not about "keep talking" lol You are welcome, Sane!!!!

[@excelisfun]

Comment from Geert Delmulle : Mike, Here's the comment KZbin doesn't seem to like me to post online: you deserve it. It's being deleted wherever I post it. To bad for that. My comment: WOW!! Just sat through the whole thing in one sitting. This video series is the ultimate overview series! All the golden knowledge nuggets neatly laid out in a brilliant string of action-packed pearls. Every bit of explanation, every interactive simulation is crafted to perfection. And the perfectly timed animations are the cherries on top of this beautiful cake. And it is such a large cake, and there are so many cherries!... The people who will learn from this have it easy: all that knowledge laid at their feet... My advise to them: open your minds and ladle it in! This is pure gold, studded with diamonds and pearls... Even for a guy like me who knows all the things you said in this video (OK, except fot that formatting website :-), this is wonderful! If at some point someone wants to take over what I do, I point them to this series, make 'm watch it, and then I'll give them a thorough exam. And if they pass, then we'll talk... Super great stuff, Mike. Fantastic and awesome! Like I said before: this feels like 2018 2.0,... and then some! Best Regards, Geert Delmulle

[@excelisfun]

Thanks for the kind words : )

[@GeertDelmulle]

You deserve it, Mike. Exceptional work deserves the recognition! :-)

[@excelisfun]

2018 2.0, but with updated user interface and more concise : )

[@wayneedmondson1065]

@@excelisfun Hi Mike (and Geert), as has happened a few times in the past, once again KZbin won't let me leave a direct public message on this video. Not sure why this happens every so often. Anyway, last night, I wrote out the post below and wanted to make sure you saw it, so putting it in this thread which I hope will be retained: Hi Mike. This lesson and video is unbelievably awesome!! I agree 100% with Geert Delmulle's comments below. I'm still on the learning curve and so it is super valuable and interesting, not to mention engaging and entertaining. I'm going through it several times and working along with the sample files which you so generously share. It really helps to be hands on vs. just in the audience. I can't commend you enough for continuing to produce this kind of outstanding material.. and all free to consume for anyone with the interest and motivation to learn :)) Go Mike and ExcelIsFun! You make all of us better at what we do, one day / video at a time. Many thanks and Thumbs up!! PS - I recently learned / deduced from your EMT 1338, for anyone who wants to bypass the UI created series of file consolidation queries, you can connect to the source folder and add your own custom column with the following which will create tables with promoted headers out of each CSV file: = Table.AddColumn(Source, "Data", each Table.PromoteHeaders(Csv.Document([Content]))). Then, Remove Other Columns, Expand the tables and Detect Data Type. So, everything done in one query with 5 steps vs. all the steps added via the UI. Thanks to your excellent lessons, I'm learning how to keep my processes compact and efficient!!

[@excelisfun]

@@wayneedmondson1065 That is such a great trick, Wayne!!!!! We'll have to call it the Wayne Method!!!!! Go Team : )

[@excelisfun]

Full List of all TOPICS in the video: 1. (00:00) Intro and overview of Standard PivotTable and Data Model Pivot Table 2. (01:15) Intro Song 3. (01:35) Intro to Relationships and SUMX Iterator Calculation to skip helper column in sales table. 4. (02:55) Reminder that building a Data Model and Learning DAX takes more time than a Standard PivotTable. 5. (03:06) 5 Steps in the Data Analysis Process 6. (03:38) Step #1: Get Data using Power Query. 7. (03:50) Look at data source for this project: Excel Tables and “.CSV” files. 8. (04:42) Reminder: Power Pivot and Power BI is tool to use for Big Data. 9. (06:12) Show Power Pivot Ribbon Tab. 10. (06:29) Power Query & Power Pivot work together as one tool. 11. (06:56) Power Query to import text “.CSV” files from a folder, including explanation of Applied Steps and automatic queries created. 12. (11:34) Loading data to the Data Model with Import Data dialog box and using the “Only Create Connection” and the “Add this data to the Data Model”. 13. (12:56) Look at data in Data Model. Preview of Power Pivot for Excel window 14. (13:20) What is a Columnar Data? See how Data is compressed to a small size so that the Data Model can handle Big Data. 15. (14:54) Don’t use Add To Data Model button. Use Power Query instead, because it has better data tools. 16. (15:16) Import Excel Tables to the Data Model using Power Query. 17. (15:29) Decimal Power Query Type is the most consistent Power Query Data Type for numbers with decimals, if you want accurate calculations. If the numbers are whole numbers, use Whole Number Power Query Data Type. 18. (17:01) Step #2: Build Star Schema Data Model. 19. (17:06) Open Data Model. 20. (17:17) Create a Power Pivot Date Table. 21. (19:29) Create One-To-Many Relationships between Fact Table and Dimension Tables. 22. (21:48) Create DAX Calculated Column in Date Table for EOMonth. This Column is used later in DAX VALUES function in first argument of the DAX AVERAGEX function. 23. (24:00) Referring to a Field or Column in DAX Formulas. 24. (24:17) First look at ROW Context to make a calculation in each row in a DAX Calculated Column (or DAX Iterator). 25. (25:28) Convention for referring to Fields and Measures. 26. (26:05) Create Total Sales Measure with 2 Step Process: 1) DAX Calculated Column for Line Sales, then 2) Measure for Total Sales. 27. (27:09) Looking up Product Price with Relationship and RELATED DAX function. 28. (28:56) Second look at ROW Context to calculate line sales in each row of the table. 29. (29:05) DAX SUM function to create Measure for Total Sales. 30. (29:25) Assignment Operator for DAX Measures. 31. (30:06) Add Number Formatting to Measure. 32. (30:25) SUMX Function, Iterator Function, to create Total Sales Measure. This is 1 Step Method. 33. (31:39) How a DAX SUMX Iterator Function works to calculate a column of values and then add. 34. (32:43) Should you use DAX Calculated Column? Or SUMX Iterator function Measure? What are trade offs? 35. (33:37) Step # 3: Build Reports. Build First Data Model PivotTable to show Year Month Sales Report. Start PivotTable from within Power Pivot for Excel window. 36. (34:48) PivotTable Fields task pane for a Data Model PivotTable. Which table is from Data Model? 37. (35:30) Show Tables in Active Tab of PivotTable Fields task pane. 38. (36:37) What is Filter Context? Visuals and explanations. 39. (38:44) How Filter Context helps with Big Data. 40. (39:55) Why some columns are not needed in PivotTable list. 41. (39:55) Why Implicit Measures are inefficient and cause problems. 42. (40:44) How to show Implicit Measures. How to delete Implicit Measures. 43. (41:50) Hide Fields. Hide From Client Tool. Hide in Report View. 44. (42:57) Sort Month Names in Data Model. 45. (43:41) Step # 4: Visualize. Create Line Chart for Year / Month Sales to see sales trends over time. 46. (45:10) Hide Measure. 47. (45:27) Create Power Pivot Data Model PivotTable from Excel Data tab. Use the PivotTable dropdown and dialog box because it is much easier than starting PivotTable from Power Pivot window. 48. (46:39) Create Regions / Year Sales Report. 49. (47:02) Create DAX Formula for YOY % Change. Learn about the DAX functions: CALCULATE, SAMEPERIODLASTYEAR, DIVIDE, HASONEVALUE and IF. 50. (47:54) CALCULATE function. Calculate Total Sales for last year. 51. (53:41) Create DAX Formula for Average Monthly Sales by Product and Year. 52. (54:28) Iterators with Tables at a certain grain in the first argument to pre-aggregate values before making an aggregate calculation. Like SUMX and AVERGAEX. 53. (54:54) Learn about VALUES function to get a unique list and deliver a table. Learn about AVERAGEX to iterate over a Month Grain Table to get Monthly Sales, and then tale the average. 54. (55:25) Create AVERAGEX formula. 55. (56:33) Context Transition explanation and diagrams to learn how AVERAGEX and VALUES and the Total Sales Measure calculates average monthly sales with a much simpler formula than in the Excel worksheet. 56. (59:07) Warning about Context Transition. Why aggregate formula yields wrong answer in Iterator function, but Measure yields correct answer. 57. (01:00:15) Hidden CALCULATE function in each Measure. CALCULATE performs Context Transition. 58. (01:01:00) Include Zero Values in average using the IF Function in the second argument of AVERAGEX. 59. (01:02:04) What to do if you accidentally add non-data model table field to PivotTable. Delete incorrectly added table. 60. (01:02:52) Create Cross Tab Report with Data Model. 61. (01:03:13) Create Frequency Distribution with YOY % Change. 62. (01:04:00) COUNTROWS DAX function. Super Charged COUNTIFS and FREQUENCY. 63. (01:04:50) Using Variables to DAX Formulas. YOY % Change for Number Transactions. 64. (01:07:45) DAX Formatter web site. 65. (01:08:08) Frequency Data Model PivotTable. 66. (01:08:39) Step # 5: Get New Data and Refresh. 67. (01:09:27) Update Power Pivot Date Table for new data. 68. (01:10:12) Look at Power Query query to verify that new files are in Data Analysis Solution. 69. (01:10:26) Sharing Power Pivot Data Model with Power BI Desktop. 70. (01:11:33) Publishing to Power BI Online and making Report from a blank Excel Workbook. 71. (01:13:46) Comparing a Power Pivot Data Model PivotTable to a Standard PivotTable. 72. (01:15:28) Summary. Review of Five Steps in Data Analysis process. 73. (01:16:04) Next Video Four video release dates: Mon, April 12: Data Analysis Class 00: Intro to PivotTable, Power Query, Power Pivot, Power BI & Visualizations, kzbin.info/www/bejne/o3fYpKB3rsaaqc0 Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts, kzbin.info/www/bejne/gqHZdJ1vq6iniKM Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling, kzbin.info/www/bejne/m3rEfHZsr7GgmJI Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling, kzbin.info/www/bejne/rHOvfmarqL6FfJY Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas, kzbin.info/www/bejne/aHvPYnp7hK17jaM This is a free four video class taught by Excel MVP, Mike Girvin, that teaches Data Analysis using the Microsoft Power Tools: Power Query, Worksheet Formulas, PivotTable, Power Pivot, Power BI & Visualizations.

[@wimsickle6173]

I created the date table, but it isn't showing up when I switch to Diagram View. Is there a step I missed? 19:29. Edit - got it, think I just needed to scroll over

[@ankursharma6157]

Token of My Deepest Gratitude (I am still watching/practicing the video, but I already feel immense respect for Your effort)! Can NEVER THANK YOU ENOUGH!

[@excelisfun]

Thank you very much for your kind donation, Ankur!!!! I am very happy that my classes help you : ) : ) : ) : )

[@alandoyle8276]

Thanks

[@excelisfun]

You are welcome, Alan!!! Thanks for your support : ) : ) : ) : )

[@fatjanpaloja9182]

I'm today DA 100 certified and have a lot experience with MS Power Pivot and Power BI (also have a lot lot to learn ☺️) but I want to thank YOU, because from your videos I got the first steps in 2013. Wish YOU all the best @Mike Girvin

[@excelisfun]

Yes!!! I love to hear that. Congratulations on being certified and accomplished! I am happy that I could help, fatjan : )

[@fatjanpaloja9182]

@@excelisfun 👍🏻👍🏻👍🏻🙏🙏🙏

[@kiwikiow]

Not only show how to click here and there to make reports or charts but also describe clearly how power tools work and advantages and disadvantages of each options. You are a great teacher. Thank you very much Mike :)

[@excelisfun]

You are welcome, kiwikiow - thanks for the kind words. But: where is that ghost... lol

[@kiwikiow]

@@excelisfun 👻 I like ExcelIsFun

[@excelisfun]

@@kiwikiow LOVE it : ) : )

[@richardhay645]

Excellent. Efficient and comprehensive presentation. Geert's comment says it all. In short: well worth the popcorn!

[@excelisfun]

Thanks, Richard!!!!!!

[@pravinshingadia7337]

Amazing work Mike - you really are one in a trillion. I've watched your videos for years and still can't believe how you do all this work and pass on your knowledge for free. Biden should be awarding you with a medal or something similar for services to your fellow man!

[@excelisfun]

For 14 years at KZbin, that has been my goal: free Excel education for the world! Thanks for your kind words, Pravin, I am glad that what I post can help you : )

[@russstinehour3858]

Outstanding, this is the best tutorial on PowerPivot I have found. You do a great job of explaining what and how with the right details. Thank you.

[@excelisfun]

Glad this helps! I have been trying to post free useful content for past 14 years at KZbin! Glad you would my channel : )

[@vanxon]

thanks for all. I improve a lot when watching your video.

[@excelisfun]

You are welcome!

[@muhammadmoiz4366]

Hi Mike ! You are the most hardworking Excel mentor I have ever seen on youtube, Sir. This video is pure gold. Every step is explained beautifully. Keep up the good work, Sir.

[@excelisfun]

You are welcome for the hard work, Muhammad! I am glad you like it all : )

[@Abooday2024]

Excellent teaching skills, you made it easy and interesting

[@excelisfun]

Glad this can help : )

[@chrism9037]

Non-stop improvement of the team, thanks as always Mike!

[@excelisfun]

I like this: non-stop improvement : ) : ) Thanks, Teammate Chris!

[@jayxpea]

just wow, one of the best demos & explanation i've come across so far!

[@excelisfun]

Glad you "wow" liked it, Jay!!!!!

[@andrewjohnson4352]

Im gonna be coming back to this video! This might be my favorite series!

[@excelisfun]

Great! If you want a short lesson in all that Microsoft Power Tools can do, this is the class. Here is the playlist with all four videos, you can bookmark it: kzbin.info/aero/PLrRPvpgDmw0lg9omfqfNczwsOryi6mwfp

[@johnborg5419]

AMAZING VIDEO: Besides the fact that I really enjoyed the DAX, I really took some time to appreciate the work from your end to show all those diagrams and sketches while explaining, which it is impossible for someone NOT to understand!!! Thanks Mike for all the hard work you put into the videos for people like me can have the chance to learn!!! : ) : ) I will definitely download this one too!!!

[@excelisfun]

Thanks for the kind words and insight like: diagrams and sketches while explaining, which it is impossible for someone NOT to understand : ) : ) : ) : ) Pictures do help in understanding!

[@carolshipley7903]

I too appreciate the skill and time taken to provide such an excellent tutorial. Thank you

[@pegmolter3542]

John, I TOTALLY agree! I've tried other PowerPivot/DAX on-line courses, and the instructors speed through it so quickly that I finally gave up since I had to keep stopping and starting the videos in order to follow. Doing that took longer than the lessons. This pace is PERFECT!

[@garethwoodall577]

Haha. Mike, you are my 'Big Data'! Love these visualisations - thank you for sharing, so helpful to learn!

[@excelisfun]

You are welcome! Sincerely, Big Data

[@Lordarknesss]

wonderfully explained, I feel so unlucky that I haven't discovered this channel until now, but also I feel so lucky to have discovered it

[@excelisfun]

I like the way you said that : ) Keep watching, learning and clicking that thumbs up ; )

[@elgennacorda4469]

Best quote I've heard in this lecture is: "We have one visualization but we visualize" 😆😆😆 Very comprehensive, thank you for sharing this valuable knowledge 💗

[@excelisfun]

You are welcome, elgen!!!! What minute mark is that quote ?

[@helent3217]

this is the best I've seen, even with an average knowledge of English. Delightful. Thanks a lot..

[@excelisfun]

You are welcome, Elena!!!! : )

[@seanmen4141]

I like this part very much, great explanation on how the context transition works under row context and filter context. This is quite hard for beginners to understand. Well done! 55. (56:33) Context Transition explanation and diagrams to learn how AVERAGEX and VALUES and the Total Sales Measure calculates average monthly sales with a much simpler formula than in the Excel worksheet.

[@excelisfun]

Glad you like the diagrams and explanation! Context Transition is invisible power in DAX ; )

[@ogwalfrancis]

Very advance, educative, easy to follow and very important video, thanks so much for always being the number one Excel solution expert🙏🙏🙏🙏🙏

[@excelisfun]

Just a humble teacher trying to make the world a better place, one Excel video at a time : )

[@ismaelkourouma5558]

Increadible lesson. Thank you Myke. I learned so much about DAX language today. I really don't think that I need to buy a book as this is too much enough. Great thanks

[@excelisfun]

Glad to help!

[@SUR-NI-VARA]

Appreciate, Nice Tutorial - I can read the pain, the efforts you have taken to reach all of us. keep it bro. Learn a lot, practice makes each on perfect..I will try out myself..Thanks again

[@excelisfun]

You are welcome!!!!!

[@HoangVu-yf1ix]

I understand the Row Context and the Filter Context now. That is great. Thank you.

[@nsanch0181]

Dang Mike! I can't believe all the content in that video. It took me 3 mornings to follow along with you, but it was worth it. Those side notes you put in there are pure gold (for example how you explained the SUMX and filter context around :38:00). I'm not sure sure I would ever have had that concept explained, if I was not part of this channel. That video was packed!. Thank you Mike.

[@excelisfun]

Those side notes you put in there are pure gold (for example how you explained the SUMX and filter context around 00:36:36 (that is correct time) that way the time hyperlink shows up : )

[@excelisfun]

You are welcome for the packed video! That is what I always try to do: give you the hows and the whys so that you can accomplish the goals, understand the concepts and then apply the skills and concepts in new situations. I am glad that you like the long videos with the hows and whys and like the content of the video. But most people do not like the content of my videos, as is evidenced by the very low view count for a free and complete Excel Data Analysis class like this.

[@syed599]

Your video contributes to enrich my career.

[@excelisfun]

I am happy to help you : ) That is why I post!!!

[@numba1druma1]

Perfect Timing!!

[@excelisfun]

Glad it is so : )

[@martinpaulbannister1745]

One of the best explanation of DAX. Thank u Mike for this resource. Countdown for video 3

[@excelisfun]

You are welcome, Martin!!!! Next Monday will be more DAX and Power BI Desktop, an add-in for Excel ; )

[@bamakaze]

Mike, thanks for the great video! I have been watching your channel for a little over two years. This is one of the best yet!

[@stephenfrey3793]

"This is char junk!" - awesome 😍

[@msantosh1220]

Eagerly awaiting for the Premiere.

[@excelisfun]

It is live!!!!!! Should be Monday fun : )

[@zahoorsarbandi2982]

Really amazing video.. a complete package for the beginner of DAX. 100/100 marks as always for each video of ExcelIsFun. I am the most lucky person who found this channel and improved a lot during last 3 months. Bundles of thanks, Mr.Mike..

[@alterchannel2501]

The Best

[@jemjoumihicham878]

An amazing video with non-stop improvement! Thank you so much Mike

[@excelisfun]

You are welcome!!

[@parshuram230682]

You are The Best Mike. Thank you for this ❤👍

[@excelisfun]

You are welcoem for this : ) What parts were the most helpful?

[@antoniogarrido3058]

Getting improving.. Really good content! Best of Data Analysis series so far.

[@excelisfun]

Glad you like it, Antonio : )

[@SantiagoMolero]

Thank you!!. You are the best teacher

[@excelisfun]

So glad to help, Santiago : ) Thanks for your kind words!!!

[@nightfrogs]

I've already learnt so much in this video and am only half way through. Thank you!

[@excelisfun]

Glad it was helpful!

[@SyedMuzammilMahasanShahi]

Wow. EXCELlent video. Thanks Mike

[@excelisfun]

You are welcome, fellow teacher : )

[@orleanCosta]

No words for thank you! I've enjoyed a lot of yours videos.

[@excelisfun]

You are welcome!!! Thanks for the "no words" awesome comment, Orlean : )

[@Hayatish94]

simply amazing

[@excelisfun]

Glad you like it : )

[@yijoey8680]

Thanks Mike, although I've not yet finished the MSPDTA lesson, when i found this video, it gives me much more clear answers (easier to understand) to the questions that i met in MSPDTA. For instance the explaination of hasonevalue (blank of year in total) ; caculate before sumx( ); year2021-ddate-values-eomonth-totalsales-region-average. i will keep learning MSPDTA and meanwhile will keep an eye on this video also. thanks again for making such a good free education resource.

[@excelisfun]

Yes, this 4 video class (full class: kzbin.info/aero/PLrRPvpgDmw0lg9omfqfNczwsOryi6mwfp), has a more clear story about data analysis and the power tools), but MSPTDA has MUCH more detail. Doing both will yield a lot : )

[@snakeeyesOFFICIAL76]

You are a genius

[@excelisfun]

Glad you like this!!!!

[@HansdeCocq]

Very clear explained difficult stuff!!! Thanks.

[@SantiagoMolero]

Thank you Mike. You are the best!! I can not believe that we still need to manually update the date range after updating the query. Shame on Microsoft developers. Thank you again!

[@excelisfun]

You are welcome, again : ) : )

[@11chance]

You are amazing!

[@excelisfun]

Glad to help you : )

[@mirrafiqul5825]

Great Work Sir

[@excelisfun]

Glad you like it, Mir : )

[@Luciano_mp]

Brilliant Mike! Thank you!

[@excelisfun]

You are welcome, Luciano!!!!!

[@florincopaci6821]

Woow! Thank you for another great video and for all the Information you share

[@ECMROHAN]

guys when i tried to download the file its just converted in to binary data can any know what to do

[@darrylmorgan]

Boom!Really Enjoyed This Super Cool Class...Thank You Mike :)

[@excelisfun]

Glad you like the Boom Class, darryl!!!!!!

[@HusseinKorish]

That's absolutely Amazing ... thanks Mike

[@excelisfun]

You are welcome, Hussein : )

[@bradleyland]

This video is pretty old, so you may have come across this bit of information by now, but the reason for the two decimal data types in Power Query has to do with precision. The decimal data type uses floating point numbers, while the currency data type uses fixed precision decimal numbers. Floating point numbers are susceptible to rounding errors that fixed precision decimal numbers are not.

[@ftmagicart]

Excellent video.Thank you very much.

[@excelisfun]

You are welcome very much for the EXCELlent video ; )

[@ljubicar1987]

Another amzing video!👏 Your explanation how measure is filtered with table pictures that you crated is always on my mind when I create formula. It is really helpful in understanding DAX!

[@excelisfun]

I am so glad that the pictures help! DAX is very difficult because it is so invisible. The pictures help you and me both: to understand the invisible power of DAX and how it works.

[@edge5817]

Thank you Mike... as always... you are amazing and truly an excel angel brought to us!

[@excelisfun]

You are welcome, Angel!!!!

[@dimmak8206]

lessons are so good thank you !

[@ivanmamchych5802]

Excellent tutorial! You managed to explain complex things in simple and comprehensive words Thanks a lot, Mike! 🤝

[@excelisfun]

That has been my goal for 13 years at KZbin: video story telling to make complex things less complex : ) I am glad that the videos work for you, Ivan!!!

[@aungthansein1820]

Nice Explanation. Thanks

[@عوضإبراهيم-ق7ج]

Great job... Thank For you sir....

[@excelisfun]

Glad you liked it and you are welcome : )

[@bravucod]

Thanks, Mike! I learn so much from you!

[@excelisfun]

Glad that I can help you to learn, Daniel!!!!!

[@xiaojienan7891]

this is the best informative video thanks very muc

[@excelisfun]

You are welcome very much!!!

[@anandraj8065]

Awesome video 🙏🙏

[@excelisfun]

Yes, glad you like it Anand!!!!

[@einoconsult5563]

Thanks Mike ! Great Stuff. 57:26 You choosed the column EOMonth in the VALUES function, why couldn't you use the Month column ?

[@excelisfun]

Because a unique list in the grand total cell would have 12 months rather than 36 ; ) Month name is not a unique identifier for the year and month category. Next video in this class, I have a visual that shows exactly that. Your question is a good question, Eino!!!!

[@einoconsult5563]

@@excelisfun Thanks Mike :) makes perfect sense once you said it 😊👌

[@excelisfun]

@@einoconsult5563 : )

[@asushitor3084]

You are the best Mike Thank you 🥰🥰

[@excelisfun]

You are welcome! I am glad that my videos help : )

[@HM-wj4je]

Excellent tutorial

[@excelisfun]

Glad it was EXCELlent for you ; )

[@mohamedadjal8502]

Thanks a lot for this video, it was good

[@excelisfun]

Glad it is good for you, mohamed!!!!

[@KumarAnalytic]

Excellent 👌

[@excelisfun]

Thanks for watching!

[@峰王]

An amazing video.

[@excelisfun]

Glad it helps!!!!

[@knikl]

Super Like (they should have a special button for that). Really great video and I enjoyed the last part about sharing between excel and power bi :) Thank you

[@excelisfun]

Thanks for the supper like : ) : ) : ) : ) Yes, I love the ability to share back and forth between Power BI and Excel!

[@sebastiannorge2980]

BIG THANK YOU 🙏

[@excelisfun]

You are welcome!

[@elbadlis]

Again, thank you for your knowledge, Amazing Mike. Your work inspire me to work harder every day. Appreciate and respect for every single detail you made on each of your video. Salute.

[@excelisfun]

You are welcome for all the detail and fun, El Badlis!!!!

[@carseyrobinson4200]

Amazing Video!! I think there may be two typos in the graphics in the Filter Context section - you mentioned that the fSales table had a total of 606 Rows, but later when the Filter Context is for 2021 the text box has 756 rows (Smaller Table) for Year 2021 and 856 rows (Smaller Table) for Year 2022. Excellent video! You explain Power Query / DAX topics very well. There is so much good content in these videos. Thanks!!!

[@darylchakamanga686]

Thanks for a great video on Power Pivot, How does one do an advanced filter in power pivot, seeing the tables will already have links?

[@khaledseghari1189]

Thanks a lot Mike

[@GaryMillerUK]

Every time Mike says 'comma' I'm hearing karma 🤣

[@excelisfun]

I love it!!!!! I should say it more literally from now on lol

[@Dessouki]

Awesome videos many thanks! Any recommendations on where to find advanced exercises and problems to put theory into practice? :)

[@GeertDelmulle]

What?! 1h15' !?! There goes over an hour of sleep, tonight! But you know what? I'm gonna cheat! I'm looking at the files -including the finished file!- already! :-D Ha! LOL! Can't wait. :-)

[@excelisfun]

Finished file does not tell the whole story, or even a small part... it is all in the animation, story line and MOST importantly the intro and outro animation ; )

[@GeertDelmulle]

@@excelisfun No, Mike. The intro and outro pale in comparison to the rest of the video!!! Awesome, simply awesome!

[@excelisfun]

@@GeertDelmulle , thanks for the kind words : ) You know all the content, though ; )

[@GeertDelmulle]

@@excelisfun I also posted another comment, but it keeps on disappearing... I'll repost it here. Looks like Mr. Excel was right: KZbin algorithms seem to be the cause...

[@GeertDelmulle]

Well, it also disappears here... I'll send it by email, then. WTF?!

[@Muuip]

Impressive! Many Thanks!👍

[@excelisfun]

Glad you like it, Muuip!!!!! Did you ever figure out the standard deviation problem?

[@Muuip]

@@excelisfun Thanks for asking! I have a working file with "getpivotdata" and implicit SD. I am now improving by choosing between: - Cube function (Analyze-Calculation-OLAP) - Power Pivot Table-Chart - DAX explicit SD:= (STDEV.S) Sofar I find DAX to be the best solution, and I am still trying to figure out how to pass the value of my explicit SD measure to the custom error bar values.🤔 Goal is a Dashboard with 4 line chart diagrams with explicit SD measure as values for the error bars.

[@excelisfun]

@@Muuip I am not sure how to pass a DAX value to error bars... I have not tried it or tried any research. If you figure it out before me, please let me know : )

[@abumohammed6387]

Very helpful thanks

[@OlesyaTango]

Love your videos Mike but I really struggled with this one. I found myself lost between your explanation of each step and continues mouse movement. I wish it was just a little slower for those of us who are so new to data modeling. I will give it another shot tomorrow maybe there is still hope for me :)

[@excelisfun]

I provide three free data analysis classes at KZbin: Basic, Intermediate and Advanced. The one you are watching is intermediate. Maybe try my basics one that is at a slower pace: kzbin.info/aero/PLrRPvpgDmw0lPPRiJO5dCUratRGpGx3aT You should be fine in the long run with all the free resources I provide to the world because I have it all from super basics to super advanced. Probably, you should have started at my KZbin channel and watched the intro video. I have posted it here for you: kzbin.info/www/bejne/omKQYpSMnKunnpY

[@OlesyaTango]

@@excelisfun Thank you for taking time to provide these resources. I am excited to learn.

[@excelisfun]

@@OlesyaTango You can do it! I really do have all levels and all topics : )

[@mailsiraj]

Thank you Mike for the phenomenal video - you have explained some of the most complex calculations like SUMX, AVERAGEX in a very nice way. Also, without making a fuss, you explained how to handle some of the product bugs like Context Transition not working. You have set up a very high baseline for creators of video with excellent annotations all throughout the video. Isn't it possible to use PQ to automatically generate date-table each time a refresh happens? The Date table solution seem ugly :).But grateful that PP has built this over the years to make life easy for non-technical users.

[@excelisfun]

Thank you for your kind words. I do work hard, and appreciate your shout out. Yes, you can use Power Query to create Data Tables. I have videos on that: kzbin.info/www/bejne/jaSaYXZppMShaNk But when I use Power Pivot, I just push through the inconvenience of having to remember to refresh the auto data table, cuz it is so easy : )

[@FRANKWHITE1996]

Great as always! Thanks :D

[@excelisfun]

You are welcome, FRANKWHITE1996!!! P.S. I always love seeing your picture because it is so happy : ) : ) : ) : )

[@RMKserve]

Amazing super explains ❤️❤️🇵🇰

[@satishthuwal8422]

Amazing as always 👏

[@excelisfun]

Glad it is so, Satish!!!

[@edme1055]

awesome!

[@excelisfun]

Glad it is awesome for you!

[@vuaus]

Video rất hữu ích. Cảm ơn rất nhiều nhé.

[@zaheerabbas97]

Hi Mike, thanks for the clear explanations. I am unable to figure it out why the average numbers are showing incorrect when i add "Month" field @ 56:26. As as example in March 2021 Carlota has 2 transactions valued 5091.5 so the Avg would be 2545.75 but in the pivot table it is showing as 5091.5, which is the total amount for that period. I have cross checked other periods as well and the behavior is same. So can you please advise where/what i am missing here. Thanks

[@rishigupta2342]

Thank you for these wonderful videos. I have a query at 54.03 mark you have taken Product and year in the rows but when I am doing the same thing it is not coming side by side as you have shown in the video? I would appreciate your response regarding this issue.In my case, Product & Year are displaying on top of each other

[@vivivioleta6245]

Hi, this is amazing tutorial, thank you. But I have question, how can I make Income Statement using DAX function and power pivot? I want to show gross profit and net profit. could you help? Thank you

[@juanfelipearangogomez7541]

Hi ! Thanks for your videos! I didn´t understand something, Why we can not use the simple way to get the YOY% Change that we use in the last video? It was easier

[@cooltady1]

Great video. however i have question since i am working with big data and lots of part number how can we find price change with dax when it occer and add column of price change % and impact on yearly budget spend. and i am try to creat spend analysis report based on vendoe, category, sub sabtgory, type of spend, and finaly part spend. can you explain or advice if you already make another video on this topic?

[@subinelias674]

You are awesome.. Thank you.

[@excelisfun]

You are welcome!

[@krimbos1]

Wow. Been searching for higher level Excel training and this is it! Great content. Question. Is there a reason you always place pivots on B3?

[@excelisfun]

No reason. Glad you like all my posts : )

[@woodyhuffman504]

Thanks for your videos I have learned so much! I know you have better things to do but I was wondering - say you have 5 different versions of sales forecasts, all with the exact same columns. If you load them into power query and stack them together, can you calculate the difference between each forecast in power pivot and manipulate the results in a pivot table without making 5 separate tables in your power pivot model?