MSPTDA 23: Two Fact Tables? DAX, Power Query or Worksheet Formulas to Convert to 1 Fact Table
Вставка
- Опубліковано 1 жов 2024
- All 9 files for video in this zipped folder: people.highlin...
Download file individually at class web site: people.highlin...
pfd notes for Video #23: people.highlin...
In this video learn about how to convert 2 Fact Tables into one with DAX, Worksheet Formulas or Power Query
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
Topics:
1. (00:16) Introduction
2. (02:35) Excel Worksheet Formula Solution
3. (05:01) How do we allocate Discount from Invoice Grain to Invoice Line Grain?
4. (05:45) Worksheet Formula for Total Invoice Sales at Invoice Grain using SUMPRODUCT function
5. (09:23) Worksheet Formula for % Sales Discount at Invoice Grain using division
6. (10:00) Worksheet Formula for Line Discount at Invoice Line Grain using VLOOKUP and multiplication
7. (12:12) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?
8. (13:27) Worksheet Formula for Invoice Line Shipping Weight at Invoice Line Grain using VLOOKUP and multiplication
9. (14:30) Worksheet Formula for Invoice Weight at Invoice Grain using SUMIFS
10. (15:04) Worksheet Formula for Line Shipping at Invoice Line Grain using VLOOKUP and multiplication
11. (16:50) Create Excel Reports at Product Grain.
12. (17:03) Standard PivotTable Report
13. (17:53) Worksheet Formula Report
14. (21:26) DAX Formula Solution in Power Pivot
15. (22:30) Look at Data Model and preview of DAX Formulas and functions SUMX, RELATED and RELATEDTABLE
16. (24:30) Bring Excel Tables into Data Model
17. (25:15) Create Relationships between tables
18. (26:23) How to Allocate Invoice Grain Numbers to Invoice Line Grain Numbers
19. (26:56) DAX Formula for Total Invoice Sales at Invoice Grain using SUMX and RELATEDTABLE functions
20. (29:40) DAX Formula for % Sales Discount at Invoice Grain using DIVIDE function
21. (30:50) DAX Formula for Line Discount at Invoice Line Grain using RELATED function and multiplication
22. (31:57) DAX Measure for Total Discount
23. (32:39) Data Model PivotTable Report for Product Discount
24. (33:08) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?+
25. (34:22) DAX Formula for Invoice Weight at Invoice Grain using SUMX, RELATEDTABLE and RELATED.
26. (35:56) Visuals to understand how DAX Formula with SUMX, REALTEDTABLE and RELATED are working to traverse multiple relationships in one formula. This helps illustrates the Power of DAX for Business Calculations.
27. (36:52) DAX Formula for Line Shipping at Invoice Line Grain using RELATED and multiplication and division. Three RELATED function in one formula
28. (39:03) DAX Measure for Total Shipping
29. (39:25) Final Data Model PivotTable
30. (40:22) Power Query Solution in Power BI Desktop
31. (41:09) Why Two Fact Tables will not work with all Dimension Tables for Reporting.
32. (43:05) Summary and visuals of steps we need to perform
33. (44:12) Create blank Power BI Desktop file
34. (44:40) Import Two Fact Table Data Model from Power Pivot
35. (45:40) Power Query Formula to calculate Sales at Invoice Line Grain using Table.AddColumn function
36. (46:53) Power Query Merge to lookup Product Weight at Invoice Line Grain
37. (47:26) Power Query Formula to calculate Product Shipping Weight at Invoice Line Grain using Table.AddColumn function
38. (47:42) Power Query Group By feature to aggregate Invoice Sales, Invoice Shipping Weight and all rows in Invoice Line Grain Table for each Invoice Number.
39. (49:52) Power Query Merge to pull Invoice Grain Shipping & Discount numbers, as well as to pull the Invoice Level Dimensions of Date and Sales Rep ID into the current step in the query (later after expanding it will be the Invoice Line Grain).
40. (50:52) Power Query Formula for % Sales Discount at Invoice Grain using Table.AddColumn function
41. (52:11) Expand to get back to Invoice Line Grain
42. (52:31) Note about Unit Price and how it is stored as a Fact because it changes so often.
43. (54:16) Power Query Formula for Line Discount at Invoice Line Grain using Table.AddColumn function and Number.Round
44. (54:49) Power Query Formula for Line Shipping at Invoice Line Grain using Custom Column using Table.AddColumn function and Number.Round
45. (57:23) Remove all column we do not need in final Fact Table
46. (58:08) Load Tables to Data Model, except Invoice Level Table.
47. (59:15) Create DAX Measures for Shipping, Discounts and Sales
48. (01:00:10) Create % DAX Measures for Shipping and Discount as a percent of sales. Use the DIVIDE DAX Function.
49. (01:01:15) Hide Columns from Report View
50. (01:01:33) Look at Final Data Model
51. (01:01:47) Create Visualization in Power BI Desktop
52. (01:04:40) Summary
Topics:
1. (00:16) Introduction
2. (02:35) Excel Worksheet Formula Solution
3. (05:01) How do we allocate Discount from Invoice Grain to Invoice Line Grain?
4. (05:45) Worksheet Formula for Total Invoice Sales at Invoice Grain using SUMPRODUCT function
5. (09:23) Worksheet Formula for % Sales Discount at Invoice Grain using division
6. (10:00) Worksheet Formula for Line Discount at Invoice Line Grain using VLOOKUP and multiplication
7. (12:12) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?
8. (13:27) Worksheet Formula for Invoice Line Shipping Weight at Invoice Line Grain using VLOOKUP and multiplication
9. (14:30) Worksheet Formula for Invoice Weight at Invoice Grain using SUMIFS
10. (15:04) Worksheet Formula for Line Shipping at Invoice Line Grain using VLOOKUP and multiplication
11. (16:50) Create Excel Reports at Product Grain.
12. (17:03) Standard PivotTable Report
13. (17:53) Worksheet Formula Report
14. (21:26) DAX Formula Solution in Power Pivot
15. (22:30) Look at Data Model and preview of DAX Formulas and functions SUMX, RELATED and RELATEDTABLE
16. (24:30) Bring Excel Tables into Data Model
17. (25:15) Create Relationships between tables
18. (26:23) How to Allocate Invoice Grain Numbers to Invoice Line Grain Numbers
19. (26:56) DAX Formula for Total Invoice Sales at Invoice Grain using SUMX and RELATEDTABLE functions
20. (29:40) DAX Formula for % Sales Discount at Invoice Grain using DIVIDE function
21. (30:50) DAX Formula for Line Discount at Invoice Line Grain using RELATED function and multiplication
22. (31:57) DAX Measure for Total Discount
23. (32:39) Data Model PivotTable Report for Product Discount
24. (33:08) How do we allocate Shipping from Invoice Grain to Invoice Line Grain?+
25. (34:22) DAX Formula for Invoice Weight at Invoice Grain using SUMX, RELATEDTABLE and RELATED.
26. (35:56) Visuals to understand how DAX Formula with SUMX, REALTEDTABLE and RELATED are working to traverse multiple relationships in one formula. This helps illustrates the Power of DAX for Business Calculations.
27. (36:52) DAX Formula for Line Shipping at Invoice Line Grain using RELATED and multiplication and division. Three RELATED function in one formula
28. (39:03) DAX Measure for Total Shipping
29. (39:25) Final Data Model PivotTable
30. (40:22) Power Query Solution in Power BI Desktop
31. (41:09) Why Two Fact Tables will not work with all Dimension Tables for Reporting.
32. (43:05) Summary and visuals of steps we need to perform
33. (44:12) Create blank Power BI Desktop file
34. (44:40) Import Two Fact Table Data Model from Power Pivot
35. (45:40) Power Query Formula to calculate Sales at Invoice Line Grain using Table.AddColumn function
36. (46:53) Power Query Merge to lookup Product Weight at Invoice Line Grain
37. (47:26) Power Query Formula to calculate Product Shipping Weight at Invoice Line Grain using Table.AddColumn function
38. (47:42) Power Query Group By feature to aggregate Invoice Sales, Invoice Shipping Weight and all rows in Invoice Line Grain Table for each Invoice Number.
39. (49:52) Power Query Merge to pull Invoice Grain Shipping & Discount numbers, as well as to pull the Invoice Level Dimensions of Date and Sales Rep ID into the current step in the query (later after expanding it will be the Invoice Line Grain).
40. (50:52) Power Query Formula for % Sales Discount at Invoice Grain using Table.AddColumn function
41. (52:11) Expand to get back to Invoice Line Grain
42. (52:31) Note about Unit Price and how it is stored as a Fact because it changes so often.
43. (54:16) Power Query Formula for Line Discount at Invoice Line Grain using Table.AddColumn function and Number.Round
44. (54:49) Power Query Formula for Line Shipping at Invoice Line Grain using Custom Column using Table.AddColumn function and Number.Round
45. (57:23) Remove all column we do not need in final Fact Table
46. (58:08) Load Tables to Data Model, except Invoice Level Table.
47. (59:15) Create DAX Measures for Shipping, Discounts and Sales
48. (01:00:10) Create % DAX Measures for Shipping and Discount as a percent of sales. Use the DIVIDE DAX Function.
49. (01:01:15) Hide Columns from Report View
50. (01:01:33) Look at Final Data Model
51. (01:01:47) Create Visualization in Power BI Desktop
52. (01:04:40) Summary
I’m so glad to be the first to comment and like the video Mr. Mike you are always the best
I should give you a first place trophy : ) : ) Thanks for the "first" support, Ismail!
Thanks, brilliant tutorial as always from you.
Fun to see that you have picked up new habits in the newer videos, ie. naming steps in Power Query without spaces and so you can tell what the step is doing.
Yup, as time goes no we keep learning : ) This trick is in my new M Code book coming out in one month.
Absolutely Brilliant! Power Query example was a home run. Thanks....
Glad you like it!
Hay I would like to thanks the author for passing my microsoft pl 300 exam. Although this course isn't aimed for pl-300 exam, this author and his videos definitely helped me to get started on power bi journey . Please continue to post amazing contents and I would like to learn more from you and thank you so much !!
Congrats on passing!!! Here is video that shows the different classes and videos that I provide for free at UA-cam:
ua-cam.com/video/GNhN1Zw8oM0/v-deo.html
Thanks for the video. I actually see a lot of this kind of layouts in DWH design. It is called "header-detail" design if I'm not mistaken. Kimball thinks this is a bad design but I guess people use it a lot.
You are welcome, Levelword! Yes, Kimball and Header-Detail... that is why the names of the Excel Tables in this example where Header and Detail ; ). It makes sense that people store data this way because the shipping and discount ARE "whole" Invoice numbers, and the units and price ARE whole product line numbers, but then it is incumbent of us, data modelers or data warehousers, to have a little fun allocating : )
Well, In the written instructions, I see you DID say to import the data model from the Excel sheet. Nice!
God save this man, who teach the people, and helping us significant!😃
I am happy to help you, Maksim!!!!
Amazing video Mike!!
Thanks for the comment and support, Chris Mancinelli!!!!
Man this is intense. I'm loving it. Thanks Mike for the share.
You are welcome for the share, Syed!!!! Thanks for the amazing support you give!!!
I am used to using CALCULATE wherever I need context transition. In the case of pulling in gross sales amount in fInvoiceHeader, firstly I created Total Sales Column in LineItmes Table, and dragged it as CALCULATE(SUM(fLineItemInvoiceDetail[Total Sales])). Is there any drawback compared to relatedtable solution?
Awesome Video from the MVP. Thanks so much. I have a congestion with DAX when doing the same formula what you did with DAX in this Video, I don't know why. Excel tell that they have errors with Invoice Sales. The errors disappear then it returns right values if I refresh all. However, I try to refresh first then typing formula, the errors still happen until I take refresh all again. Could you tell me the reason ?
Thanks Mike for repeating these videos, merging them into one lesson and integrating all of it into the MSPTDA class.
They fit nicely in this course, and it was necessary to watch them again.
BTW: I explain the DAX formulas to myself in terms of Context and Context Type (row/filter), which is just a little different than the way you explained it, but (of course) it boils down to the exact same thing. Just to practice the DAX way of thinking.
See you on the next one!
I like the way you think! Thinking in DAX so so much different then in Worksheet Formulas or Power Query, in a good way : ) Thanks for your support, Geert!
Mike, the other day in Excel Classic, I was writing a formula and I wrote it from a DAX-perspective: it gives you a new way of thinking about the old stuff. The formula was this: =MAX(LEN(SomeRange)). It’s a single cell calculation (and we like that kind of stuff!) and I really saw it as an aggregator on a dynamically (and in-cell) constructed range (column). Two things/aspects I learned from the DAX environment.
Of(f) course, I still need CTRL+SHIFT+ENTER, but still: in the past I would have used a helper column.
BTW, I know: it’s a known Excel technique called “lifting”, but still: it makes for a powerful, compact and efficient workbook (and a lot of jaws dropping on the floor when people ask me to ‘mildly’ explain it :-). They always say the same: “they thought they knew Excel, but they didn’t know “this” “. (Where “this” is a variable). Yeah, well: whatever gets the job done, right? :-)
(Single cell computations yield very elegant spreadsheets :-)
@@GeertDelmulle Love that story, Geert!!!! Yes, Single Cell, whether conceptualized in Worksheet or DAX, is still awesome!!! Thanks for being so smart with Excel and treating the people at your work with fun surprises : )
Related and relatable is awesome 👌..you have given 3 ways to solve that problem ..I like the third one ...by creating star schema model in BI ...thanks a lot ❤️
Yes, sir!!! Me too. RELATEDTABLE is just so beautiful : ) : )
What's the most efficient way to do it for the computer (Power Pivot VS Power Query)?
Oh, it's nice that VLOOKUP locked the lookup table :)
Thanks Mike!
You are welcome, Dave! Thanks for your consistent support : )
A common problem solved, thank you!
You are welcome, Sally Lee! Thanks for your uncommon support : )
Transforming Excel people to think like DAX!! BANG ON....Most importantly we should have Star Schema rather than Snowflake!!
Bang Boom!!!!
Thanks for the eye-opening lesson. In the Power BI segment, I used Import to transfer the data model from the Excel file into the Power BI file. Is that a method that you go over later in the MSPTDA series?
You know, I thought I taught it in MSPTDA #17, but I just looked and it is not there!?!?!?! I guess I did only there very minimum by including it in one line in the pdf notes : (
It is great to know when you already have Data Model in Excel. Then you can have the best of both worlds, Excel Data Model PivotTable, then bring Data Model over to Power BI for some publishable interactive visuals !!!
wonderful thanks for the way and the good steps to learn
The 2nd part of this video was absolutely amazing! I need to fix a couple of my models after learning that. Thanks!!
Glad you like it!
I am really impressed by the quality of your work! Amazing tutorial!!!
Glad it is amazing for you, kostas! Thank you for your support : )
Do you prefer using DAX or Power Query on this issue? I've been using Power Query but it seems that the DAX way is better. What do you think?
DAX is often faster.
Just out of interest I noticed that in the excel sumif formula where you lock the structured reference for criteria range, but for criteria itself you used the locked cell reference,
is it possible to absolute @[Product] , I've tried every which way I can think of.
Maybe INDEX([Product],1) if it was the first cell.
After a very hard working week, this was great fun !!!! Thanks Mike. :)
Sorry about your hard work week! Glad this provided some fun, John Borg!!!
links are dead :(
Now I know how to lock a cell in an Excel table. Thank you :)
Glad you found one of the small details in the video that was helpful, Keisha! Thanks for watching and thanks for your support!
Can I say how awesome the PDF download is? thank you Mike!
Yes you can!!! You are welcome... I hope you have seen some of the other pdf notes too : )
top
thanks for all
the files sample is very easy to learn
thanks very much
thnaks
You are welcome - by now you should be getting to be an Excel Data Analysis Master, Rusdi : ) : )
when i try to use Group by it gives an errors,,, specifically when i added last aggregation.,,,for all records...Can you tell what i am doing wrong? Greatful
Awesome video, thank you so much Mike
You are welcome, Ogwal!!
Thank you for your great selfless.
I have a sales table and a budget table for a network of stores. I need to create a relationship between the two tables so that I can be able to see both budget and actual sales per month per store but the relationship just can't happen. May you please assist?
I will post a video next week on this topic : )
You aer welcome for the resources. Thanks for your support, Daison!!!!
thanks again for this material.
You are welcome!
Warning! No popcorn! ...
Niiiiiiiiiiiiiice!!! : )
Nice one
Thanks
You are welcome!
Good work!
: )
Awesome tutorial 👍👍👍. I tend to get confused when switching between Excel and DAX. But this video really helps to call out the difference . Millions thanks Mike 👍😁😁
Million you are welcomes!!! My specialty is linking DAX, Data Model and Power Query between Excel, Excel Power Pivot and Power BI : )
Now I think no one will choose methods other than the third one, great video mike for data modeling..and don't forget to check your power bi desktop it's said that there is a new update :)
Yes, I like the Power Query Method also, but the DAX formulas are amazing : ) Thanks for the reminder about updating my Power BI - you are smartly observant!
B/fast on new year's day. Happy new year mike, i wish you and all your followers on yr channel more fun with yr coming videos. :) :)
Perfect breakfast for new years days: 2 Fact Tables form the excelisfun restaurant : ) : )
Top👍👍👍
Glad you like it!!!!
This video is one of the best. Congatulations!!!
Glad it helps : )
I just started watching the video but I wanted to thank you first for such an informative and clear educational video! I really appreciate your time and effort for putting together the guides.
You are we4lcome, Celia : )
This was an awesome video with the 3 different methods to solving this problem. Great tutorial!
Glad you like it, TP : )
Another awesome lesson, thank you so much Mike.
You are welcome!
Hello Mr Mike i have one question can u plz help me out..
I wana simply sumif values on merged cells..
Like i have data A2:B20.. A2:A20 is criteria range where some names are merged and B2:B20 is sum range.. I wanna sumif the values based on criteria where some cells are merged. When am trying to do this its giving the only sum of first value of each merged cells.
I have no idea how to do that. Merged cells are usually not a good idea because they cause trouble in many different ways.
@@excelisfun thanks for replying.
@@excelisfun Buy the way u r realy good instructor, ur teaching mathod is very easy and clear.. I appreciate your struggle..
@@mrexcel173 , Thanks! I appreciate your support on each video : )
Mike amazing video as always!
Quick question for you and any viewers of the channel. Is there a shortcut key to jump you back to a sheet when you a doing a formula that references multiple worksheets?
So I'm doing a countifs on sheet 2 that references a range on sheet 1 as one of my criteria range. When I go and select this range on sheet 1 is there a shortcut key that takes me back to the formula on sheet 2?
To jump back to a Precedent Cell, try Ctrl + [
To jump to a Dependent Cell, try Ctrl + ]
Glad you like the video, Brain! Thanks for your support with your comments, Thumbs Ups and Sub : )
@@excelisfun Thanks so much Mike,, you da man!!
Anyone know how to change all the dates in cells by updating in just first cell?
It depends on how you want all the dates to change.
ExcelIsFun I need all the dates in sequence i.e from 5/1/2019 to 5/31/2019, so when change date in first cell all dates need to change automatically. If you have any video please share link.
@@nasnooka If First Date is in cell A1, then just use formula like =A1+1, then copy down : )
Do you like the videos?
ExcelIsFun I been watching you from long time. Learn lots of new things.
Thanks it works.
Plz make videos on excel dashboard
I have so many videos on that topic... Here is a playlist about dashboards, where you can choose what video to watch: ua-cam.com/play/PLrRPvpgDmw0kYt4c50Sg7BXGLBAjiW6VQ.html , maybe the "Highline Class #17" video (3rd in playlist) or (E-DAB #8 video #15 in the playlist).
Please help support this free resource, Abhishek, with your comments and thumbs ups on each video and your Sub : )
Hi Girvin, I want only in excel, in some office only use of Ms excel in place of power bi,
@@AbhisheakSaraswat As I said in previous videos, there at least two good videos for you : )
@@excelisfun which one, if you don't mind plz share that link, it would be great for me. Your help really appreciated.
@@AbhisheakSaraswat I already posted it above!!!