Главная
Study mode:
on
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)
Description:
Learn advanced Excel data analysis techniques using Power Pivot and Power BI in this comprehensive 58-minute video tutorial. Master big data analysis by working with real-world datasets, including Seattle's 911 calls and SQL databases. Explore Power Query for data import and transformation, create dimension tables, and develop DAX formulas for complex calculations. Dive into data modeling, relationship creation, and visualization techniques using various chart types, maps, and word clouds. Gain hands-on experience with filter contexts, calculated measures, and custom number formatting. By the end of this tutorial, acquire the skills to build powerful reports, understand the differences between ALL and ALLSELECTED DAX functions, and efficiently handle large datasets in both Power BI and Power Pivot environments.

Big Data Analysis with Power Pivot & Power BI - Visuals & DAX Formulas

ExcelIsFun
Add to list
#Business #Business Software #Microsoft Office 365 #Microsoft Excel #Business Intelligence #Microsoft Power BI #DAX (Data Analysis Expressions)
0:00 / 0:00
1 of 71

) Intro