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!!!
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!!
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
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
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)!
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!
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: UA-cam algorithms seem to be the cause...
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!!!!
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
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 :)
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!
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 💗
sir, I know you made a video of cumulative count streak column (reset count after event), I cant recall if it was on power pivot or power query but I can't find it😭 Could you help me find it?
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
I could try to make a longer comment, but I don't think that that could even convey my gratitude in a satisfactory way. I'll just say "Thank you!" once again. On an unrelated note, your intro kind of reminds me of the game Manifold Garden. ua-cam.com/video/vLt4ZXcDdIQ/v-deo.html
I have a full 2013 class for you: Excel 2013 PowerPivot Playlist of Videos ua-cam.com/play/PLrRPvpgDmw0nGCx21PRFbsJpUIH06LKs-.html See you in the comments at this new class : ) : ) : ) : )
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
Hi, I am looking for guidance on how to handle the following. I am looking to remove client duplicates and then lookup a unique client in a column to a unique list of users in columns across. One client may have several users. At the moment i got in an excel file: Col A (USERS) a list of several users names that i would need to mapped to a large list of client listed in several columns across (Col B: Client 1, Client 2, Client 3, Client 4....), (col C: Client 3, Client 4..) (Col D: Client 2, Client 4, Client 5...) The user list is unique but the client names are duplicated in some of the columns. How can i remove the client duplicates, list them in col A (Unique Clients) for instance and then across (col B- 1 A, col C: user 2, col D: user 3, and so on)the users assigned to each client. Note the user can be linked to more than one client. Hope i did not overcomplicate it. Thank you in advance
I just noticed that the data is from the US and I am in Europe so the issue comes with the dates file. When I want to create EOmonth and select dates it says syntax error. How can I change date types?
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.
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?
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
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!
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
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.
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
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
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 : )
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.
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.
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 : )
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 : )
@@excelisfun Yes Mike I use it every day :-) yes it is a mean of saving a lot of time and making our word done perfectly :-) Good news I take in last february the microsoft office specialist in excel :-) thanks for making us mastering excel tricks
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
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.
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?
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
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 : )
Excellent tutorial
Glad it was EXCELlent for you ; )
Excellent 👌
Thanks for watching!
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
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
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
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!
Non-stop improvement of the team, thanks as always Mike!
I like this: non-stop improvement : ) : ) Thanks, Teammate Chris!
guys when i tried to download the file its just converted in to binary data can any know what to do
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: UA-cam algorithms seem to be the cause...
Well, it also disappears here... I'll send it by email, then. WTF?!
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 : )
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!!!!!
You are The Best Mike.
Thank you for this ❤👍
You are welcoem for this : ) What parts were the most helpful?
just wow, one of the best demos & explanation i've come across so far!
Glad you "wow" liked it, Jay!!!!!
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 : ) : )
Excellent. Efficient and comprehensive presentation. Geert's comment says it all. In short: well worth the popcorn!
Thanks, Richard!!!!!!
I just wondering why that solo dislike is there.
Glad you like the video, Dr. Sofyan Hayajneh. Thanks for your support : )
"This is char junk!" - awesome 😍
Boom!Really Enjoyed This Super Cool Class...Thank You Mike :)
Glad you like the Boom Class, darryl!!!!!!
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!!!!
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 ?
sir, I know you made a video of cumulative count streak column (reset count after event), I cant recall if it was on power pivot or power query but I can't find it😭 Could you help me find it?
ua-cam.com/video/PFRYwUVpZgg/v-deo.html
@@excelisfun thenk you sir, thank you very much
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
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!
I could try to make a longer comment, but I don't think that that could even convey my gratitude in a satisfactory way. I'll just say "Thank you!" once again.
On an unrelated note, your intro kind of reminds me of the game Manifold Garden.
ua-cam.com/video/vLt4ZXcDdIQ/v-deo.html
Glad it all helps : ) That intro you sent is cool.
@@excelisfun I never actually played the game, but I did watch a playthrough and I've been meaning to get it...
Hi sir please send link its all related topic old version I have office 2013 version so plz 👍🏻👍🏻👍🏻👍🏻👏
I have a full 2013 class for you:
Excel 2013 PowerPivot Playlist of Videos
ua-cam.com/play/PLrRPvpgDmw0nGCx21PRFbsJpUIH06LKs-.html
See you in the comments at this new class : ) : ) : ) : )
@@excelisfun hey tq so mch mr mike:):):)👍🏻💯💯💯💯💯💯💯💯👌
Eagerly awaiting for the Premiere.
It is live!!!!!! Should be Monday fun : )
Every time Mike says 'comma' I'm hearing karma 🤣
I love it!!!!! I should say it more literally from now on lol
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 teaching skills, you made it easy and interesting
Glad this can help : )
this is the best informative video thanks very muc
You are welcome very much!!!
An amazing video with non-stop improvement!
Thank you so much Mike
You are welcome!!
thanks for all. I improve a lot when watching your video.
You are welcome!
Hi, I am looking for guidance on how to handle the following. I am looking to remove client duplicates and then lookup a unique client in a column to a unique list of users in columns across. One client may have several users. At the moment i got in an excel file: Col A (USERS) a list of several users names that i would need to mapped to a large list of client listed in several columns across (Col B: Client 1, Client 2, Client 3, Client 4....), (col C: Client 3, Client 4..) (Col D: Client 2, Client 4, Client 5...) The user list is unique but the client names are duplicated in some of the columns. How can i remove the client duplicates, list them in col A (Unique Clients) for instance and then across (col B- 1 A, col C: user 2, col D: user 3, and so on)the users assigned to each client. Note the user can be linked to more than one client. Hope i did not overcomplicate it. Thank you in advance
please i am using excel 2016 please will the DAX formulas work in the version I am using.
Hi can I ask how can I exclude the calculation of zeros after I divide 2 columns values using POWER PIVOT I really have a bad time to do it Thank you
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 : )
At 18:29 what can I do when I dont have an option ,,date table"? I have only ,,mark as date table"... :( @ExcellsFun
Why does the function sumx (31:11) not work in my excell? I've already written the same logical sentence with you.,
I just noticed that the data is from the US and I am in Europe so the issue comes with the dates file. When I want to create EOmonth and select dates it says syntax error. How can I change date types?
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.
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?
Hey Mike facing the error "The following system error occurred:Class not registered" and it failed to import the features :(
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
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 : ) : ) : ) : )
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
Haha. Mike, you are my 'Big Data'! Love these visualisations - thank you for sharing, so helpful to learn!
You are welcome! Sincerely, Big Data
Woow! Thank you for another great video and for all the Information you share
Very clear explained difficult stuff!!! Thanks.
After make pivot table, I want to calculate the difference between 2 subtotal column. How can I do it?
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 : )
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!
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 : )
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!
Thanks!
Thank you very much, Elena!!!! Your donation helps me to keep making videos : ) : ) : ) : )
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 : )
The Best
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.
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 ; )
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 : )
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 ; )
Thanks a lot for this video, it was good
Glad it is good for you, mohamed!!!!
Amazing super explains ❤️❤️🇵🇰
Great job...
Thank For you sir....
Glad you liked it and you are welcome : )
Video rất hữu ích. Cảm ơn rất nhiều nhé.
this is the best I've seen, even with an average knowledge of English. Delightful. Thanks a lot..
You are welcome, Elena!!!! : )
Getting improving.. Really good content! Best of Data Analysis series so far.
Glad you like it, Antonio : )
:( cannot find the get data option in Microsoft 2013
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 : )
While creating Power Pivot Date Table it is taking month in place of date and date in place of month e.g. 02.01.2021 as 1st February 2021
That si just Number Formatting. You can change that : )
That's absolutely Amazing ... thanks Mike
You are welcome, Hussein : )
Brilliant Mike! Thank you!
You are welcome, Luciano!!!!!
This can be made only by a person who is breathing Excel as Mike Girvin :-)
Thanks for the kind words, Mohamed Chakroun!!!!!
I think that you also Breathing Excel, Mohamed ; ) It helps us to get our job done with more fun!!!
@@excelisfun Yes Mike I use it every day :-) yes it is a mean of saving a lot of time and making our word done perfectly :-)
Good news I take in last february the microsoft office specialist in excel :-) thanks for making us mastering excel tricks
@@mohamedchakroun4973 Congratulations on the office specialist in Excel, Mohamed!!!!!
@@excelisfun Thankss mike :-)
Excellent video.Thank you very much.
You are welcome very much for the EXCELlent video ; )
Again I got stucked 🥺 36:37 my pivot didn’t show table and instead of rows and column, it shows Axis and legend. Pls what could be the problem
You missed something again. For PivotTables, go to Design, Layout, Report Layout, Show in Tabular Form.
Nice Explanation. Thanks
An amazing video.
Glad it helps!!!!
Perfect Timing!!
Glad it is so : )
BIG THANK YOU 🙏
You are welcome!
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
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 : )
You are amazing!
Glad to help you : )
No words for thank you! I've enjoyed a lot of yours videos.
You are welcome!!! Thanks for the "no words" awesome comment, Orlean : )
Awesome video 🙏🙏
Yes, glad you like it Anand!!!!
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.
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
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?
simply amazing
Glad you like it : )
Awesome videos many thanks! Any recommendations on where to find advanced exercises and problems to put theory into practice? :)
Your video contributes to enrich my career.
I am happy to help you : ) That is why I post!!!
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?
Hi Mike, How do DAX calculate day on Day changes excluding holidays and weekends? Thanks!
thanks Mike the last link for Excel sheet does not work
can you give us another link
The servers had a catastrophic error and it will be a few days until I can get it back up... : (
About the non-refreshing dDate table: can’t the columns be added to the fSales table and thereby solve the issue?
I understand the Row Context and the Filter Context now. That is great. Thank you.
Thank you!!. You are the best teacher
So glad to help, Santiago : ) Thanks for your kind words!!!
Very helpful thanks