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