Solution for Two Fact Tables: Power Query or DAX formulas or Worksheet Formulas? 365 MECS Class 18
Вставка
- Опубліковано 1 лип 2024
- Create Reports & Visuals from Two Fact Tables with Different Grains. 3 Solutions. 365 MECS Class 18
Download Zipped Folder with All Files: excelisfun.net/files/18-M365E...
50 pages of pdf notes: excelisfun.net/files/18-M365E...
This video shows how to deal with Two Fact Tables with Different Grains. Learn three solutions: 1) Power Query, DAX Formulas or Worksheet Formulas. Learn how to create reports and visuals from Two Fact Tables with Different Grains.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: • Microsoft 365 Excel & ...
This video covers.
1. (00:00) Introduction and video topics
2. (00:47) Fundamental Problem with Two Fact Tables with Different Grains
3. (04:50) Look at Three Methods to solve problem: 1) Worksheet Formulas and Standard PivotTable, 2) DAX Formulas, or 3) Power Query Transformation
4. (06:08) Allocating Invoice Grain Discount and Ship Costs Down to Smaller Line Item Grain
5. (07:40) Worksheet Formulas and Standard PivotTable Solution. Lots of XLOOKUP and SUMIFS functions in various helper columns to create a flat table that can be used in a Standard PivotTable.
6. (17:46) DAX Formula Solution (Calculated Columns and Measures) to allocate amounts and create a single Fact table at the correct grain.
7. (27:12) Power Query Solution (in Power BI Desktop) to transform the data into an efficient Star Schema Data Model.
8. (36:30) Video Summary and Conclusions
9. (36:56) Practice Problem / Homework Problem
10. (37:07) Closing and Video Links
Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. creativecommons.org/licenses/... . Artist: audionautix.com/
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #powerbi #powerquery #powerbidesktop #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #datamodel #DAX #powerquery #sumifs #xlookup #related #datamodel #fact
I love it, you did really a fantastic job! There is no other XL Entertrainer like our famous Mike! 🎖🎖🎖 🎺🎺🎺
I am always happy to entertain and try to make the complicated less complicated with a good performance, Roger!!!
Mike, you are enlightening the Excel world
Always glad to help!!!! : )
Mike the Master once again! Thanks for more coaching, appreciate the videos.
You are welcome for the videos!!!!
I just posted the Homework / Practice files at 7:15 AM Pacific Time May 31 : ) They are in a folder called "Homework".
Two related Fact Tables certainly make life interesting!! I currently maintain two such "Data Duplexes" with Spreadsheet fomulas. Data analysis in this environment is far easier with the ever expanding set of 365 functions. I look foward to seeing how you approach these challenges!!
Yes, this should be a fun one because we solve it three different ways! Formulas, especially with M 365, are a great way to go! But the DAX formulas are pretty amazing and of course Power Query just takes the non-confirmative data and makes it good : )
Absolutely awesome! Thanks Mike ! waiting for the next video !!!
Glad it is awesome for you!!!! Next one is two fact tables also: budget and actual...
Excellent as always. Thanks Mike👍
You are welcome!!!!
Mike, again very good! You do not see many Excel video's with two fact tables. I recognise this video example from the invoice header and details, a time ago. There you calculated marging of the different produccts. But now with this video it was very nice to see the 3 different options in comparisson. Nice trick to see how a help column can be used to make a DAX formula. Also thumbs up the way you explained every step with an object in your video editor!
Yes, good eye: Bart!!! A number of examples in this class are from the MSPTDA class I posted about 5 years back. But I am hoping that the story I am telling and the order of the examples and other new details will help reveal the complications as less complicated as compared to my older videos : )
BTW, what did you mean when you wrote: "way you explained every step with an object in your video editor". I am not sure what you mean. What is the object? I do not remember showing my video editor?
@@excelisfun I meant the box/rectangle you added later with explanations as text. Well a rectangle is an object you can move...😉😉
@@barttitulaerexcelbart9400 Cool! Thanks : ) : ) : ) : )
I already know this is something I need to watch! 😄
It will be triple the fun : ) : ) : )
Super Mega Great Excel Journey!!✌
Thanks, O Master ExcelLambda : ) : ) : ) : )
Thank you!
You are welcome!!!
Hi Mike, since Dsaleresp and Ddate can flow to invoice line item level , via invoice level fact table, since it will from from one to many side from dsalesrep to finvocielevel and again from one to many from invoice level to invoiceline item level, and in that you can bring in product table as well. so we have all three dimension in one pivot.
Egarly waiting for the release.
Me too : ) : ) : ) : ) : )
Thank you Mike for this awesome video. A super nice summary 👌
You are welcome! Another Two Fact Table MECS video will be out later today : )
@@excelisfun fantastic Mkke 👏
@@nadermounir8228 : ) : )
MUCH WAITED VIDEO SIR
I hope it will be good for you!!!! : )
This one’s gonna be good Mike!
Hope so : ) : ) : )
Just watched it Mike, totally epic!!
@@chrism9037 : ) : ) : ) : )
Good job dear
Glad you like it : ) : ) : )
Great lesson as always! For some time I haven't been able to create a Calculated Field in Pivot Tables, however I can create Measure under the Power Pivot group with the formula
[Sum of LineDiscount]/[Sum of LineSales]
which produced the same results. I am on the Insider edition, but this has been an issue for months.I was able to see the Calculated Field in the Finished version, so I doubt that's the problem. Do I have a setting causing this? TIA!
I do not know of a setting. I am not sure why that is happening... : (
Smart moving to make a Measure!
Power Query and Power BI best part
: ) : ) : ) : ) Power Query is THE data transformational tool - so much fun!
I am just getting into Data Analytics. I’m pretty good with Excel, but do you recommend knowing Power BI or SQL next?
SQL is everywhere, but Power BI is important also. It depends on what you are doing.
@@excelisfun Thanks to you. Right now, learning Power BI since I'm good with Excel. From your perspective, what would you recommend for a newbie to focus on that employers seek when learning Power BI?
@@Chewpa_Miverga_786 Well, I am a technician and always believe that if you are not awesome with M Code (the functional language behind Power Query) and DAX (the functional language behind metrics in visuals) why are you even using Power BI ? Did you already study the 17 MECS videos before this one?
@@excelisfun I saw you were live, so I wanted to send you a message. Yes, I’m working on the MECS program you have on UA-cam. You’re a legend 😎
@@Chewpa_Miverga_786 Yes!!! Love to hear you are working through MECS : )
Hi sir, thank you for sharing your knowledge to the world. I've learned so much from your videos. I also have a question, at minute 19:50 u created a calculated column on fInvoice table (dimension table) and compute the PercentInvoiceDiscount using the RELATEDTABLE, I am just confused because u didn't used CALCULATE to force context transition to happen yet the calculation delivers the correct amount, somehow it sees the invoice no in each row and used it as a filter to fLineInvoice without using CALCULATE. I'am confused because in MECS 17 u also made calculated column in a dimension table and havent used calculate and the calculation gives wrong result as context transition does not happen, it needs to be wrap inside the CALCULATE to deliver the correct result. Appreciate if you can clarify, i really to learn this topic. Anybody is also welcome to help. Much live.
Do you have a slow pace tutorial for beginners somewhere in your archive please?
This is the near last video in my most advanced class - so the pace assumes you know almost everything. What topic do you want? I have many free classes. Here is Excel Basics free class:
ua-cam.com/play/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k.html
😄 👌👍
: ) : ) : )
Hello, how could i reach you for a zoom meeting discusing one hour asking you some advices about one of my project?
I just see another scenario where the required star schema is not as obvious as ist should be. Imagine your products are batch managed and the batch numbers are not unique, meaning "Sunbell" can have the same batch reference as "Quad". Sooner or later, you realize only the combination of product code and batch code is a unique identifier. I'm curious how you would manage this challenge, if statistic on batch level and product level is required.
I guess it seems we would need to create a single table with a combination of product code and batch code as the unique identifier with all numbers... I am not really sure, as I am not understanding the situation.