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)!
Comment from Geert Delmulle : Mike, Here's the comment UA-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 UA-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!!
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 :)
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, ua-cam.com/video/mFusmAyagsk/v-deo.html Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts, ua-cam.com/video/LpvCj9vCtRA/v-deo.html Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling, ua-cam.com/video/eIaKC6zLmb0/v-deo.html Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling, ua-cam.com/video/vBLM3usYRF4/v-deo.html Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas, ua-cam.com/video/2Jl1GEOHHWA/v-deo.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 UA-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: ua-cam.com/play/PLrRPvpgDmw0lg9omfqfNczwsOryi6mwfp.html
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!
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.
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!
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
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 💗
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.
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
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.
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..
That has been my goal for 13 years at UA-cam: video story telling to make complex things less complex : ) I am glad that the videos work for you, Ivan!!!
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.
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: ua-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 : )
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!
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?
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
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.
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!!!!
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: ua-cam.com/video/Ws70C3o_n2w/v-deo.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 : )
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
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?
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
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, I have a question. What I noticed, is that dimension tables or fact tables always have a column which contain primary key or foreign key ; even in other tutorials have same thing. So is it like that always, I mean in real world scenario did we always get data where we have already primary or foreign key. Or we have to built data sets in which we have to determine which should be the primary or foreign key. Please answer. Thanks
It has to be like that to get lookup formulas in an Excel worksheet, Relationships in the Data Model or Merges in Power Query and SQL. If it is not, you would have to create it. In the Excel Data Analysis #4 video you will see that we do not have a foreign key or primary key for an Hour dimension table, and so we make one : ) Further, it should not seem strange that dimension tables (lookup tables to Excel people) and a Fact table with a foreign key exist, because you can't lookup something up whether a price for a product or a grade in a class or an employees' e-mail address if there is not a unique list in the lookup table. It is just common sense who things work : )
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!!!
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 UA-cam: Basic, Intermediate and Advanced. The one you are watching is intermediate. Maybe try my basics one that is at a slower pace: ua-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 UA-cam channel and watched the intro video. I have posted it here for you: ua-cam.com/video/l1-1aVgFth4/v-deo.html
By sharing the data model you also share the calculated measures. However if the user does not understand the filter context under which the measure was designed the user can get an incorrect result. Right? Does the receiving the data model have the ability to see the actual formula of the calculated columns? Thanks
If you have a star schema data model, with Measures like Total Sales and so on work fine whether or not the user understands filter context. But they have to have a version that has Power Pivot.
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.
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.
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.
Amazing Class as always! Just a quick question, for the YOY % Change, when I checked in my data model Pivot table, it seemed to have the option to calculate the "Show value as % difference", why do we then need to go over all the DAX calculations if we have the same functionality through the pivot table? Thanks
As stated in the video, invoking the "Show Values As..." creates only an Implicit Measure (Calculation). This is not preferable because it is a "read-only" measure. It is not modular for DM Pivot Table purposes like a proper DAX/Explicit Measure.
@@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 : )
Hello again Mike, great video! Though you did not cover the fact that some measures might not give you the correct total value, you still used HASONEVALUE in a smart way that at least gives me an idea of how to use it. Of course, I have already experienced this so to me it is intuitive, but for others it might not. So to them - pay extra attention to the HASONEVALUE function ;-) I have a question regarding your relationships with product information. You use the same price across four years, which I would say is unrealistic. How would you go about creating the same relation, but with four different product information tables? Would you simply append them and create a unique key combination of Product ID and Year? Or if prices change each semester, there would be eight different product information tables. Would you then append them and create a unique key combination of Product ID and Year+semester? Thank you again for the great amount of knowledge you provide us, you are amazing!
It is not necessarily unrealistic to have same price for 4 years. However, prices do change. In video #4 in this class you will see one situation that deals with this when you have a revenue discount and standard Net Cost Equivalent to deal with the changing prices. Also, the most common way to deal with it is to have a lookup table with dates, then in DAX you can use LOOKUPVALUE with two lookup values or in Excel worksheet you can use XLOOKUP (with an join as you suggested) or even in some cases to get last price MAXIFS.
Yes, HASONEVALUE is the function to use on a column where only the grand total cell has multiple values and you want the grand total cell to show blank. ISFILTERED can be used on a column that has one value when the field appears, but multiple items when other fields appear.
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
Comment from Geert Delmulle
:
Mike,
Here's the comment UA-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 UA-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 : )
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 : ) : )
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 👍🏻👍🏻👍🏻🙏🙏🙏
Excellent. Efficient and comprehensive presentation. Geert's comment says it all. In short: well worth the popcorn!
Thanks, Richard!!!!!!
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, ua-cam.com/video/mFusmAyagsk/v-deo.html
Mon, April 19: Excel Data Analysis Class 01: PivotTable, Power Query, Formulas and Charts, ua-cam.com/video/LpvCj9vCtRA/v-deo.html
Mon, April 26: Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships & Data Modeling, ua-cam.com/video/eIaKC6zLmb0/v-deo.html
Mon, May 03: Excel Data Analysis Class 03: Power BI Desktop, DAX Formulas, Relationships & Data Modeling, ua-cam.com/video/vBLM3usYRF4/v-deo.html
Mon, May 10: Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot, Power BI & DAX Formulas, ua-cam.com/video/2Jl1GEOHHWA/v-deo.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
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 UA-cam! Glad you would my channel : )
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 UA-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!!!!!
Non-stop improvement of the team, thanks as always Mike!
I like this: non-stop improvement : ) : ) Thanks, Teammate Chris!
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: ua-cam.com/play/PLrRPvpgDmw0lg9omfqfNczwsOryi6mwfp.html
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 ; )
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 : )
thanks for all. I improve a lot when watching your video.
You are welcome!
Haha. Mike, you are my 'Big Data'! Love these visualisations - thank you for sharing, so helpful to learn!
You are welcome! Sincerely, Big Data
I understand the Row Context and the Filter Context now. That is great. Thank you.
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 : ) : ) : ) : )
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!
this is the best I've seen, even with an average knowledge of English. Delightful. Thanks a lot..
You are welcome, Elena!!!! : )
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!
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 ?
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 ; )
Excellent teaching skills, you made it easy and interesting
Glad this can help : )
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.
Your video contributes to enrich my career.
I am happy to help you : ) That is why I post!!!
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!!!!!
I've already learnt so much in this video and am only half way through. Thank you!
Glad it was helpful!
An amazing video with non-stop improvement!
Thank you so much Mike
You are welcome!!
You are The Best Mike.
Thank you for this ❤👍
You are welcoem for this : ) What parts were the most helpful?
Perfect Timing!!
Glad it is so : )
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.
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..
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 UA-cam: video story telling to make complex things less complex : ) I am glad that the videos work for you, Ivan!!!
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 ; )
Getting improving.. Really good content! Best of Data Analysis series so far.
Glad you like it, Antonio : )
Eagerly awaiting for the Premiere.
It is live!!!!!! Should be Monday fun : )
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: ua-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 : )
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 : ) : )
"This is char junk!" - awesome 😍
Thanks!
Thank you very much, Elena!!!! Your donation helps me to keep making videos : ) : ) : ) : )
Thank you!!. You are the best teacher
So glad to help, Santiago : ) Thanks for your kind words!!!
You are a genius
Glad you like this!!!!
The Best
Thank you Mike... as always... you are amazing and truly an excel angel brought to us!
You are welcome, Angel!!!!
simply amazing
Glad you like it : )
Very clear explained difficult stuff!!! Thanks.
You are amazing!
Glad to help you : )
Woow! Thank you for another great video and for all the Information you share
Boom!Really Enjoyed This Super Cool Class...Thank You Mike :)
Glad you like the Boom Class, darryl!!!!!!
lessons are so good thank you !
Brilliant Mike! Thank you!
You are welcome, Luciano!!!!!
Wow. EXCELlent video. Thanks Mike
You are welcome, fellow teacher : )
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?
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
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!!!!
You are the best Mike
Thank you 🥰🥰
You are welcome! I am glad that my videos help : )
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?
That's absolutely Amazing ... thanks Mike
You are welcome, Hussein : )
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 : )
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
Thanks, Mike! I learn so much from you!
Glad that I can help you to learn, Daniel!!!!!
Excellent video.Thank you very much.
You are welcome very much for the EXCELlent video ; )
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: ua-cam.com/video/Ws70C3o_n2w/v-deo.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 : )
Nice Explanation. Thanks
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
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?
Hi Mike, when it comes to the averagex on the sales, is the 2020 year been filter down by the date column or the year Column? Thanks
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
Awesome video 🙏🙏
Yes, glad you like it Anand!!!!
Great Work Sir
Glad you like it, Mir : )
Excellent tutorial
Glad it was EXCELlent for you ; )
this is the best informative video thanks very muc
You are welcome very much!!!
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?
No reason. Glad you like all my posts : )
Awesome videos many thanks! Any recommendations on where to find advanced exercises and problems to put theory into practice? :)
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!
Hi Mike, I have a question. What I noticed, is that dimension tables or fact tables always have a column which contain primary key or foreign key ; even in other tutorials have same thing. So is it like that always, I mean in real world scenario did we always get data where we have already primary or foreign key. Or we have to built data sets in which we have to determine which should be the primary or foreign key. Please answer. Thanks
It has to be like that to get lookup formulas in an Excel worksheet, Relationships in the Data Model or Merges in Power Query and SQL. If it is not, you would have to create it. In the Excel Data Analysis #4 video you will see that we do not have a foreign key or primary key for an Hour dimension table, and so we make one : ) Further, it should not seem strange that dimension tables (lookup tables to Excel people) and a Fact table with a foreign key exist, because you can't lookup something up whether a price for a product or a grade in a class or an employees' e-mail address if there is not a unique list in the lookup table. It is just common sense who things work : )
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!!!
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 UA-cam: Basic, Intermediate and Advanced. The one you are watching is intermediate. Maybe try my basics one that is at a slower pace: ua-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 UA-cam channel and watched the intro video. I have posted it here for you: ua-cam.com/video/l1-1aVgFth4/v-deo.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 : )
Excellent 👌
Thanks for watching!
Great job...
Thank For you sir....
Glad you liked it and you are welcome : )
By sharing the data model you also share the calculated measures. However if the user does not understand the filter context under which the measure was designed the user can get an incorrect result. Right? Does the receiving the data model have the ability to see the actual formula of the calculated columns? Thanks
If you have a star schema data model, with Measures like Total Sales and so on work fine whether or not the user understands filter context. But they have to have a version that has Power Pivot.
BIG THANK YOU 🙏
You are welcome!
Thanks
You are welcome, Alan!!! Thanks for your support : ) : ) : ) : )
Thanks a lot for this video, it was good
Glad it is good for you, mohamed!!!!
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.
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 :)
An amazing video.
Glad it helps!!!!
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.
Amazing Class as always!
Just a quick question, for the YOY % Change, when I checked in my data model Pivot table, it seemed to have the option to calculate the "Show value as % difference", why do we then need to go over all the DAX calculations if we have the same functionality through the pivot table?
Thanks
As stated in the video, invoking the "Show Values As..." creates only an Implicit Measure (Calculation). This is not preferable because it is a "read-only" measure. It is not modular for DM Pivot Table purposes like a proper DAX/Explicit Measure.
Thanks a lot Mike
I watched your videos about a year ago, I like it Thanks, I want to know how to use the match function in conditional formatting in Excel
What is the goal of the conditional formatting? To see if an item is in a list? or not in a list?
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 : )
Very helpful thanks
Hello again Mike, great video! Though you did not cover the fact that some measures might not give you the correct total value, you still used HASONEVALUE in a smart way that at least gives me an idea of how to use it. Of course, I have already experienced this so to me it is intuitive, but for others it might not. So to them - pay extra attention to the HASONEVALUE function ;-)
I have a question regarding your relationships with product information. You use the same price across four years, which I would say is unrealistic. How would you go about creating the same relation, but with four different product information tables? Would you simply append them and create a unique key combination of Product ID and Year? Or if prices change each semester, there would be eight different product information tables. Would you then append them and create a unique key combination of Product ID and Year+semester?
Thank you again for the great amount of knowledge you provide us, you are amazing!
It is not necessarily unrealistic to have same price for 4 years. However, prices do change. In video #4 in this class you will see one situation that deals with this when you have a revenue discount and standard Net Cost Equivalent to deal with the changing prices. Also, the most common way to deal with it is to have a lookup table with dates, then in DAX you can use LOOKUPVALUE with two lookup values or in Excel worksheet you can use XLOOKUP (with an join as you suggested) or even in some cases to get last price MAXIFS.
Yes, HASONEVALUE is the function to use on a column where only the grand total cell has multiple values and you want the grand total cell to show blank. ISFILTERED can be used on a column that has one value when the field appears, but multiple items when other fields appear.
@@excelisfun Thank you so much for this information, there is so much to learn! I truly appreciate what you do for this community.
@@fredrikarntsen5880 You are welcome : )
Amazing as always 👏
Glad it is so, Satish!!!