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. 😎
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!!!
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)!
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!
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 :)
Comment from Geert Delmulle : Mike, Here's the comment TH-cam 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 Hi Mike (and Geert), as has happened a few times in the past, once again TH-cam 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!!
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!
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
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, th-cam.com/video/mFusmAyagsk/w-d-xo.html Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts, th-cam.com/video/LpvCj9vCtRA/w-d-xo.html Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling, th-cam.com/video/eIaKC6zLmb0/w-d-xo.html Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling, th-cam.com/video/vBLM3usYRF4/w-d-xo.html Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas, th-cam.com/video/2Jl1GEOHHWA/w-d-xo.html 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.
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
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!
For 14 years at TH-cam, 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 : )
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: th-cam.com/play/PLrRPvpgDmw0lg9omfqfNczwsOryi6mwfp.html
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.
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!!!
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!
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!
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 💗
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
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
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.
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.
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 : )
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.
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..
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!
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.
Yes, this 4 video class (full class: th-cam.com/play/PLrRPvpgDmw0lg9omfqfNczwsOryi6mwfp.html), has a more clear story about data analysis and the power tools), but MSPTDA has MUCH more detail. Doing both will yield a lot : )
That has been my goal for 13 years at TH-cam: video story telling to make complex things less complex : ) I am glad that the videos work for you, Ivan!!!
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!
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.
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!!!!
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.
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
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
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.
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: th-cam.com/video/Ws70C3o_n2w/w-d-xo.html 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 : )
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. :-)
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 ; )
@@excelisfun I also posted another comment, but it keeps on disappearing... I'll repost it here. Looks like Mr. Excel was right: TH-cam algorithms seem to be the cause...
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!!!
Thanks a lot for vid. 57:44 I'm still struggling to understand how does PP realises, that EOMonth value means "please show for WHOLE month, not for single date of EOMonth". Hope I clearly described my problem
Becasue the unique identifier EOMONTH is lasted for each day in the month. Each row for q given month is marked with that EOMONTH date, so when filter filters table, only the records for that month are showing. Get it now?
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
@@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.
@@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 : )
Brilliant video as always. My problem is around the date hierarchy that is generated from the calendar table. Most of my reports are weekly based. I can create an ISO weeks calculated column, but it would be more convenient if 'week number' was a part of the date hierarchy. Am I missing a trick regarding how I can automatically include the week number in a calendar table or a time based measure?
I don't know an automatic way. I would delete the auto Hierarchy, then in Diagram View, I would select the date columns that should be in Hierarchy and right-click, Hierarchy.
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?
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?
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.
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 :)
I provide three free data analysis classes at TH-cam: Basic, Intermediate and Advanced. The one you are watching is intermediate. Maybe try my basics one that is at a slower pace: th-cam.com/play/PLrRPvpgDmw0lPPRiJO5dCUratRGpGx3aT.html 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 TH-cam channel and watched the intro video. I have posted it here for you: th-cam.com/video/l1-1aVgFth4/w-d-xo.html
Great video thanks so much! When writing the Total Sales ($) measure, I was taught to do it with the CALCULATE function. CALCULATE(SUM(fSales[Line Sales]),dProduct). Is there a benefit to using the SUMX function rather than CALCULATE?
The two formulas are TOTALLY different. The SUMX measure in this video is created in Measure grid and calculates total revenue and allows the filter context in the PivotTable area to influence the Measure. I am not sure what you are trying to do with this formula: CALCULATE(SUM(fSales[Line Sales]),dProduct). CALCULATE changes the filter context and you have a table filter, which is sometimes used to traverse a one-to-many relationship from the many side to the one side? What are you trying to do with your formula? Where are you using your Measure: in a PivotTable, an iterator, a Calculated Column? You said "I was taught to do it with CALCULATE". Who taught you? The Measure that we created in this video, like all Measures, automatically has a hidden CALCUALTE, so in that sense, the SUMX formula would not need CALCULATE unless we wanted to change the filter context, like we did with the SAMEPERIODLASTYEAR function.
@@excelisfun Thanks for getting back to me. I learned this from Rob Collie in his PowerPivotPro course. I used it in the first Pivot Table you created (PivotTable3 on the DataSets sheet of EDA02-FinishedAfterVideoFilmed.xlsx) and got the same results. So I was wondering which method was faster, more efficient? Unfortunately, You Tube will not let me upload a screenshot.
@@richardgreves6161 The SUMX is correct. The CALCULATE(SUM(fSales[Line Sales]),dProduct) is not correct. The CALCULATE(SUM(fSales[Line Sales]),dProduct) formula unnecessary because: 1) The [Total Sales ($)] Measure by itself has a hidden CALCULATE and 2) you do not need to change the filter context with the dProduct table filter by using CALCULATE.
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
Hello Mike, Thanks for this video, it was a good one as usual. As my company did not invest yet in power BI, I am obliged to make dashboard on Excel. I found a trick avoid to used the inbuild calendar that does not update by itself... ;( I create in power query a "blank query" with the following M code. And load in the data model. Then I add manually columns as you would do in power BI. So even if a 2025 csv file arrives... no problem, the M code calendar in the data model updates automatically. The end user does not need to update the build-in calendar max in the data model (which is out of skills from most of end users) ____________ let Source = {Int64.From( List.Min(#"fSales"[Date]) ).. Int64.From(List.Max(#"fSales"[Date]) )}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}) in #"Changed Type" ___________ Hope it make sense ;)
@@Anthony_Lecoq I am happy to help you!!! I hope that you thumbs up and comment often so that the free Excel education can reach even more people in the world : )
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.
😎
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!!!
@@excelisfun Thank you for those very kind words, and dang, they were in some fresh verses!
barsss
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)!
Thanks for your kind words : ) Always glad to help!
LOL in the last month there was over 1/2 million unique viewers
Thanks!
Thank you very much, Elena!!!! Your donation helps me to keep making videos : ) : ) : ) : )
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!
Yah, telling a good story is not about "keep talking" lol You are welcome, Sane!!!!
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 :)
You are welcome, kiwikiow - thanks for the kind words. But: where is that ghost... lol
@@excelisfun 👻 I like ExcelIsFun
@@kiwikiow LOVE it : ) : )
Comment from Geert Delmulle
:
Mike,
Here's the comment TH-cam 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
Thanks for the kind words : )
You deserve it, Mike. Exceptional work deserves the recognition! :-)
2018 2.0, but with updated user interface and more concise : )
@@excelisfun Hi Mike (and Geert), as has happened a few times in the past, once again TH-cam 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!!
@@wayneedmondson1065 That is such a great trick, Wayne!!!!! We'll have to call it the Wayne Method!!!!! Go Team : )
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!
Thank you very much for your kind donation, Ankur!!!! I am very happy that my classes help you : ) : ) : ) : )
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
Yes!!! I love to hear that. Congratulations on being certified and accomplished! I am happy that I could help, fatjan : )
@@excelisfun 👍🏻👍🏻👍🏻🙏🙏🙏
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.
Glad this helps! I have been trying to post free useful content for past 14 years at TH-cam! Glad you would my channel : )
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, th-cam.com/video/mFusmAyagsk/w-d-xo.html
Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts, th-cam.com/video/LpvCj9vCtRA/w-d-xo.html
Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling, th-cam.com/video/eIaKC6zLmb0/w-d-xo.html
Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling, th-cam.com/video/vBLM3usYRF4/w-d-xo.html
Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas, th-cam.com/video/2Jl1GEOHHWA/w-d-xo.html
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.
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
Excellent. Efficient and comprehensive presentation. Geert's comment says it all. In short: well worth the popcorn!
Thanks, Richard!!!!!!
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!
For 14 years at TH-cam, 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 : )
just wow, one of the best demos & explanation i've come across so far!
Glad you "wow" liked it, Jay!!!!!
Haha. Mike, you are my 'Big Data'! Love these visualisations - thank you for sharing, so helpful to learn!
You are welcome! Sincerely, Big Data
Non-stop improvement of the team, thanks as always Mike!
I like this: non-stop improvement : ) : ) Thanks, Teammate Chris!
thanks for all. I improve a lot when watching your video.
You are welcome!
Im gonna be coming back to this video! This might be my favorite series!
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: th-cam.com/play/PLrRPvpgDmw0lg9omfqfNczwsOryi6mwfp.html
Thanks
You are welcome, Alan!!! Thanks for your support : ) : ) : ) : )
Excellent teaching skills, you made it easy and interesting
Glad this can help : )
Very advance, educative, easy to follow and very important video, thanks so much for always being the number one Excel solution expert🙏🙏🙏🙏🙏
Just a humble teacher trying to make the world a better place, one Excel video at a time : )
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.
You are welcome for the hard work, Muhammad! I am glad you like it all : )
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!!!
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!
I too appreciate the skill and time taken to provide such an excellent tutorial. Thank you
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!
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
I like the way you said that : ) Keep watching, learning and clicking that thumbs up ; )
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!
this is the best I've seen, even with an average knowledge of English. Delightful. Thanks a lot..
You are welcome, Elena!!!! : )
I understand the Row Context and the Filter Context now. That is great. Thank you.
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 💗
You are welcome, elgen!!!! What minute mark is that quote ?
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
You are welcome!!!!!
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
Glad to help!
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.
Glad you like the diagrams and explanation! Context Transition is invisible power in DAX ; )
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.
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 : )
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.
One of the best explanation of DAX. Thank u Mike for this resource. Countdown for video 3
You are welcome, Martin!!!! Next Monday will be more DAX and Power BI Desktop, an add-in for Excel ; )
Your video contributes to enrich my career.
I am happy to help you : ) That is why I post!!!
"This is char junk!" - awesome 😍
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..
An amazing video with non-stop improvement!
Thank you so much Mike
You are welcome!!
I've already learnt so much in this video and am only half way through. Thank you!
Glad it was helpful!
You are The Best Mike.
Thank you for this ❤👍
You are welcoem for this : ) What parts were the most helpful?
Getting improving.. Really good content! Best of Data Analysis series so far.
Glad you like it, Antonio : )
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!
You are welcome, again : ) : )
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.
Yes, this 4 video class (full class: th-cam.com/play/PLrRPvpgDmw0lg9omfqfNczwsOryi6mwfp.html), has a more clear story about data analysis and the power tools), but MSPTDA has MUCH more detail. Doing both will yield a lot : )
Eagerly awaiting for the Premiere.
It is live!!!!!! Should be Monday fun : )
No words for thank you! I've enjoyed a lot of yours videos.
You are welcome!!! Thanks for the "no words" awesome comment, Orlean : )
Excellent tutorial!
You managed to explain complex things in simple and comprehensive words
Thanks a lot, Mike! 🤝
That has been my goal for 13 years at TH-cam: video story telling to make complex things less complex : ) I am glad that the videos work for you, Ivan!!!
Woow! Thank you for another great video and for all the Information you share
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!
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.
Thank you Mike... as always... you are amazing and truly an excel angel brought to us!
You are welcome, Angel!!!!
Thank you!!. You are the best teacher
So glad to help, Santiago : ) Thanks for your kind words!!!
Perfect Timing!!
Glad it is so : )
Very clear explained difficult stuff!!! Thanks.
Thanks Mike ! Great Stuff.
57:26 You choosed the column EOMonth in the VALUES function, why couldn't you use the Month column ?
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!!!!
@@excelisfun Thanks Mike :) makes perfect sense once you said it 😊👌
@@einoconsult5563 : )
Boom!Really Enjoyed This Super Cool Class...Thank You Mike :)
Glad you like the Boom Class, darryl!!!!!!
Thanks, Mike! I learn so much from you!
Glad that I can help you to learn, Daniel!!!!!
Wow. EXCELlent video. Thanks Mike
You are welcome, fellow teacher : )
this is the best informative video thanks very muc
You are welcome very much!!!
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.
You are welcome for all the detail and fun, El Badlis!!!!
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
Thanks for the supper like : ) : ) : ) : ) Yes, I love the ability to share back and forth between Power BI and Excel!
lessons are so good thank you !
That's absolutely Amazing ... thanks Mike
You are welcome, Hussein : )
You are the best Mike
Thank you 🥰🥰
You are welcome! I am glad that my videos help : )
Excellent video.Thank you very much.
You are welcome very much for the EXCELlent video ; )
Brilliant Mike! Thank you!
You are welcome, Luciano!!!!!
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
Nice Explanation. Thanks
Thanks a lot for this video, it was good
Glad it is good for you, mohamed!!!!
You are amazing!
Glad to help you : )
Great job...
Thank For you sir....
Glad you liked it and you are welcome : )
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?
simply amazing
Glad you like it : )
Awesome video 🙏🙏
Yes, glad you like it Anand!!!!
Great Work Sir
Glad you like it, Mir : )
You are a genius
Glad you like this!!!!
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.
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: th-cam.com/video/Ws70C3o_n2w/w-d-xo.html
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 : )
The Best
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. :-)
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 ; )
@@excelisfun No, Mike. The intro and outro pale in comparison to the rest of the video!!!
Awesome, simply awesome!
@@GeertDelmulle , thanks for the kind words : ) You know all the content, though ; )
@@excelisfun I also posted another comment, but it keeps on disappearing...
I'll repost it here. Looks like Mr. Excel was right: TH-cam algorithms seem to be the cause...
Well, it also disappears here... I'll send it by email, then. WTF?!
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!!!
Thanks a lot for vid.
57:44 I'm still struggling to understand how does PP realises, that EOMonth value means "please show for WHOLE month, not for single date of EOMonth".
Hope I clearly described my problem
Becasue the unique identifier EOMONTH is lasted for each day in the month. Each row for q given month is marked with that EOMONTH date, so when filter filters table, only the records for that month are showing. Get it now?
@@excelisfun seems I got. Thank you very much.
@@emilbek_isaev Pretty cool how that Filter Context works : )
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
Impressive! Many Thanks!👍
Glad you like it, Muuip!!!!! Did you ever figure out the standard deviation problem?
@@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.
@@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 : )
Excellent tutorial
Glad it was EXCELlent for you ; )
Amazing as always 👏
Glad it is so, Satish!!!
Brilliant video as always. My problem is around the date hierarchy that is generated from the calendar table.
Most of my reports are weekly based. I can create an ISO weeks calculated column, but it would be more convenient if 'week number' was a part of the date hierarchy.
Am I missing a trick regarding how I can automatically include the week number in a calendar table or a time based measure?
I don't know an automatic way. I would delete the auto Hierarchy, then in Diagram View, I would select the date columns that should be in Hierarchy and right-click, Hierarchy.
@@excelisfun Sounds good. Thanks Mike :)
Awesome videos many thanks! Any recommendations on where to find advanced exercises and problems to put theory into practice? :)
Excellent 👌
Thanks for watching!
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?
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?
BIG THANK YOU 🙏
You are welcome!
Amazing super explains ❤️❤️🇵🇰
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.
Why does the function sumx (31:11) not work in my excell? I've already written the same logical sentence with you.,
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 :)
I provide three free data analysis classes at TH-cam: Basic, Intermediate and Advanced. The one you are watching is intermediate. Maybe try my basics one that is at a slower pace: th-cam.com/play/PLrRPvpgDmw0lPPRiJO5dCUratRGpGx3aT.html
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 TH-cam channel and watched the intro video. I have posted it here for you: th-cam.com/video/l1-1aVgFth4/w-d-xo.html
@@excelisfun Thank you for taking time to provide these resources. I am excited to learn.
@@OlesyaTango You can do it! I really do have all levels and all topics : )
Great as always! Thanks :D
You are welcome, FRANKWHITE1996!!! P.S. I always love seeing your picture because it is so happy : ) : ) : ) : )
An amazing video.
Glad it helps!!!!
Late to th party MIke, Sir please may I know how to get the CSV, you were referring to in 04:26 it appears that the attachments are updated.
Show more area below video has all links
@@excelisfun Thank you sir, I forgot to update the comment but I am sure it will help all. Thanks a ton Mike.
Great video thanks so much! When writing the Total Sales ($) measure, I was taught to do it with the CALCULATE function. CALCULATE(SUM(fSales[Line Sales]),dProduct). Is there a benefit to using the SUMX function rather than CALCULATE?
The two formulas are TOTALLY different. The SUMX measure in this video is created in Measure grid and calculates total revenue and allows the filter context in the PivotTable area to influence the Measure. I am not sure what you are trying to do with this formula: CALCULATE(SUM(fSales[Line Sales]),dProduct). CALCULATE changes the filter context and you have a table filter, which is sometimes used to traverse a one-to-many relationship from the many side to the one side? What are you trying to do with your formula? Where are you using your Measure: in a PivotTable, an iterator, a Calculated Column? You said "I was taught to do it with CALCULATE". Who taught you? The Measure that we created in this video, like all Measures, automatically has a hidden CALCUALTE, so in that sense, the SUMX formula would not need CALCULATE unless we wanted to change the filter context, like we did with the SAMEPERIODLASTYEAR function.
@@excelisfun Thanks for getting back to me. I learned this from Rob Collie in his PowerPivotPro course. I used it in the first Pivot Table you created (PivotTable3 on the DataSets sheet of EDA02-FinishedAfterVideoFilmed.xlsx) and got the same results. So I was wondering which method was faster, more efficient? Unfortunately, You Tube will not let me upload a screenshot.
@@richardgreves6161 The SUMX is correct. The CALCULATE(SUM(fSales[Line Sales]),dProduct) is not correct. The CALCULATE(SUM(fSales[Line Sales]),dProduct) formula unnecessary because: 1) The [Total Sales ($)] Measure by itself has a hidden CALCULATE and 2) you do not need to change the filter context with the dProduct table filter by using CALCULATE.
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
Very helpful thanks
Video rất hữu ích. Cảm ơn rất nhiều nhé.
Hello Mike,
Thanks for this video, it was a good one as usual. As my company did not invest yet in power BI, I am obliged to make dashboard on Excel.
I found a trick avoid to used the inbuild calendar that does not update by itself... ;(
I create in power query a "blank query" with the following M code. And load in the data model.
Then I add manually columns as you would do in power BI.
So even if a 2025 csv file arrives... no problem, the M code calendar in the data model updates automatically. The end user does not need to update the build-in calendar max in the data model (which is out of skills from most of end users)
____________
let
Source = {Int64.From( List.Min(#"fSales"[Date]) ).. Int64.From(List.Max(#"fSales"[Date]) )},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
#"Changed Type"
___________
Hope it make sense ;)
Very cool solution!!!! Thanks for the post so others can see it : )
@@excelisfun Mike, all credits goes to you as I learned most of m code and DAX through your videos ;)
@@Anthony_Lecoq I am happy to help you!!! I hope that you thumbs up and comment often so that the free Excel education can reach even more people in the world : )
At 18:29 what can I do when I dont have an option ,,date table"? I have only ,,mark as date table"... :( @ExcellsFun