Excel Data Analysis Class 04: BIG Data Analysis with Power Pivot & Power BI: Visuals & DAX Formulas

Posted on by Bb

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)

ExcelIsFunThu, August 5, 2021 8:31pm