Videos about Excel Microsoft Power BI Desktop from Mike excelisfun Girvin at Highline College.
Curated by: excelisfun (61 videos)
Download File: http://people.highline.edu/mgirvin/excelisfun.htm Excel Magic Trick 1366 Full Lesson on Power BI Desktop to build Product Analysis for Gross Profit with Average, Standard Deviation, Coefficient of Variation and Histogram Calculations and Visualizations: 1. (00:04) Files to download 2. (00:12) Introduction 3. (04:42) Import Related Tables from Access 4. (05:42) Edit automatic Relationships Bi-directional Filtering to Single-directional Filtering 5. (07:22) Import Text Files From Folder 6. (08:36) Filter out file extensions that are NOT Text .txt 7. (09:38) Use “Combine Binary” Icon to combine Text Files into one table 8. (10:40) Look at “Combine Binary”: Query Creation Steps, including M Code and Power Query Function that is automatically created 9. (12:23) Change Data Types in fSales (Fact Sales) Table 10. (13:23) edit Relationship between fSales Product Table 11. (14:14) Create Calendar Table in Excel 12. (18:33) Create Frequency Distribution Category Table in Excel using Text Formula 13. (21:39) Import tables from Excel File 14. (22:52) Manually Create Relationships Between Tables 15. (23:40) Create DAX Calculated Column for Net Revenue using the RELATED function (works like VLOOKUP Exact Match in Excel) & ROUND function. Net Revenue values are stored in the “In RAM Memory” Data Model 16. (25:40) Discuss Convention for using Columns in formulas: ALWAYS USE TABLE NAME AND COLUMN/FIELD NAME IN SQUARE BRACKETS 17. (26:24) Look at How REALTED works across relationships 18. (27:07) Discussion of Row Context 19. (29:25) Create Measure for Total Revenue. This Measure is a Measure that is based on values in a Calculated Column 20. (31:15) Add Number Format to Measure so that every time the Measure is used the Number Format will appear 21. (31:53) Learn about Measures that are not dependent on Calculated Columns. See how to create Measure that does not use a Calculated Column as a source for values. UseSUMX function 22. (34:59) and (36:40) Compare creating: 1) Measures based on Calculated Columns and or Measures not based on Calculated 23. (35:39) and (42:40) Discussion of Filter Context and how it helps DAX formulas calculate Quickly on Big Data. Filter Context: When a Conditions or Criteria are selected from the Lookup Tables (Dimension Tables) they flow across the Relationships from the One-Side to the Many-Side to Filter the Fact Table down to a smaller size so that the formulas have to work over a smaller data set 24. (36:52) and (37:52) Discussion of how values created in Calculated Colum are stored in the Data Model Columnar Database and this uses RAM Memory 25. (38:54) When you must use a Calculated Column: When you need to extend the data set and add a column that has Conditions or Criteria that you want to use to Filter the Data Set 26. (40:06) Create Calculated Column For COGS using ROUND and RELATED Functions 27. (41:50) Create Calculated Column for Gross Profit 28. (43:35) Create Calculated Column on fSales Table that will create the Sales Categories “Retail” or “Wholesale” using IF & OR functions. Because it creates Criteria that will use as Filters for our Measures, This DAX formula can only be created using a Calculated Column, not a Measure 29. (46:00) Measure for Total COGS 30. (46:36) Measure for Total Gross Profit 31. (47:20) Measure for Gross Profit Percentage. This is a Ratio of two numbers. This is an example of a Measure that can ONLY be created as a Measure. It cannot be created as a Measure based on a Calculated Column 32. (48:35) Discuss Convention for using Measures in other Measures: USE SQUARE BRACKETS ONLY around the Measure name 33. (49:52) Measure for Average (Mean) Gross Profit 34. (50:20) Measure for Standard Deviation of the Gross Profit 35. (51:09) Measure for Coefficient of Variation of the Gross Profit 36. (52:43) Hide Unnecessary Columns from Report View 37. (53:01) Sort Month Name Column by Month Number 38. (54:19) Sort Category Column By Lower Limit 39. (55:25) Add Data Category Image URL for Image File Paths 40. (57:10) Create DAX Column to simulate Approximate Match Lookup using the FLOOR function 41. (59:54) Manually Create Relationship For Category Table 42. (01:00:18) Update Excel Table and Test to see if Power BI Report Updates when we Refresh 43. (01:01:57) Create Product Analysis Visualization with the first visualization: Create Table with Product Pictures and Metrics. This is Page one of our Power BI Report. 44. (01:03:13) Create Bar Chart For Mean and Standard Deviation of Gross Profit 45. (01:03:39) Create Slicers to Filter Visualizations 46. (01:04:11) Create Frequency Distribution Table & Measure to Count Transactions 47. (01:05:35) Format Table, Chart and Slicers 48. (01:07:45) Create second Page in Power BI Report with Product Revenue and COGS by Year & Month 49. (01:09:05) Publish Power BI Report online 50. (01:10:37) Generate Embed code for e-mailing Report and for embedding in web sites 51. (01:11:38) Summary