Videos about Excel Microsoft Power BI Desktop from Mike excelisfun Girvin at Highline College.
Curated by: excelisfun (61 videos)
Excel Data Analysis 04 files: https://excelisfun.net/files/EDA04-Start.xlsx https://excelisfun.net/files/EDA04-Finished.xlsx https://excelisfun.net/files/EDA04-ReportsFinished.pbix https://excelisfun.net/files/EDA03-Reports-FinishedAfterVideoFilmed.pbix Download Power BI Desktop: https://powerbi.microsoft.com/en-us/desktop/ Topics in the video: 1. (00:00) Intro 2. (00:38) Overview of data and visuals we will do in Power BI 3. (02:05) Overview of SQL data and reports we will build in Power Pivot 4. (02:58) Start of Power BI Big Data CSV file Example 5. (03:12) Download 911 Seattle data from web site: https://data.seattle.gov/Public-Safety/Seattle-Real-Time-Fire-911-Calls/kzjm-xkqj 6. (03:56) Power Query to Import and transform csv file 7. (04:30) Data Source Settings 8. (05:15) Data Modeling in Power Query: create foreign key columns in Fact Table 9. (07:27) Remove fields not needed to make Columnar Database smaller. 10. (08:14) Close and Apply data to Data Model 11. (08:36) Create Date and Time Dimension Tables 12. (08:47) copy GENERATE and ROW DAX Formula from last video to create the Data Table. 13. (09:54) GENERATESERIES DAX Function to create Hour Dimension Table 14. (10:35) Create Serial Number Time DAX Calculated Field 15. (10:58) Create Hour DAX Calculated Field 16. (11:36) Sort Hour Field by Hour ID field 17. (11:46) Sort Month Name in Data Table by Date field 18. (11:54) Set Hour field to NOT sum 19. (12:01) Create Relationships 20. (12:23) Hide fields 21. (12:35) Mark data table as Date Table 22. (13:00) Create DAX Measure for counting using COUNTROWS DAX Function. 23. (14:00) Hide Foreign keys in Fact Table 24. (14:31) Create Table Visuals to show counts 25. (15:07) Sort fields in tables 26. (15:37) Add Slicers 27. (16:05) Create a Card Visual to show Measure result 28. (17:34) Understanding Filter Context 29. (18:46) Create DAX Measure for % of Grand Total using CALCULATE, ALL & DIVIDE functions 30. (21:37) () Create DAX Measure for % of Filtered Grand Total using CALCULATE, ALLSELECTED and DIVIDE functions 31. (22:13) Difference between ALL and ALLSELECTED DAX functions 32. (23:16) “See” Filtered Total created by ALLSELECTED function 33. (24:46) Create MAP Visual to show location of 911 calls 34. (26:32) Create Word Cloud 35. (27:32) ArcGIS Time Movie Map Visual 36. (29:23) Update when new data arrives 37. (29:52) Power BI Project conclusion 38. (30:46) Start of Power Pivot Big Data SQL Database Example 39. (30:53) Reporting Goal explained 40. (31:10) SQL Database explained 41. (31:46) Look at tables and data for project 42. (32:32) Credentials to connect to the SQL Database 43. (34:08) Navigator dialog box 44. (34:41) Power Query Editor for the three SQL tables 45. (35:00) Extra Columns from related tables 46. (36:12) Look at Fact Table and adjust Data Types, including “Using Locale” option for dates with different settings 47. (37:10) View Native Query inside Power Query Applied Steps to see which steps are sent back to SQL database (folding) 48. (38:32) Load tables to Power Pivot Data Model’s Columnar Database 49. (40:00) Create Power Pivot Date Table 50. (42:04) Create Relationships 51. (42:37) Create DAX Measures 52. (42:54) Compare DAX Calculated Columns to DAX Measures. 53. (43:58) Total Revenue Measure using SUMX DAX function 54. (44:41) What is Revenue Discount? How to use in formula? 55. (45:54) Use Filter Dropdown to see a unique list 56. (46:27) What is Net Standard Cost, or Net Cost Equivalent? How to use in formula? 57. (47:41) Total COGS Measure using SUMX DAX function 58. (48:51) Gross Profit ($) DAX Measure 59. (49:29) Gross Profit % DAX Measure 60. (50:06) Average Daily Gross Profit DAX Measure. Discuss Pre-aggregating at the day grain before averaging the daily totals 61. (51:56) Why are $ signs in Measure name, rather than using Number Formatting with dollar signs? 62. (52:28) Hide Fields from Client Tool 63. (53:02) Build Data Model PivotTable Gross Profit Report 64. (53:50) Add Slicer to Data Model PivotTable 65. (54:14) Create Line Chart to show Gross Profit over Year and Month 66. (55:20) Create Custom Number Format in Chart 67. (56:00) Change Chart type from Column to Line 68. (56:14) Add series of numbers to Secondary Axis 69. (56:27) Connect Chart to Slicer with Report Connections 70. (56:55) Power Pivot Project Conclusion 71. (57:17) Conclusion for whole video (both movies)