Completed
) Info about files for project
Class Central Classrooms beta
YouTube videos curated by Class Central.
Classroom Contents
Basic Excel Business Analytics - Comprehensive PowerPivot, Data Model, DAX & Reporting Example
Automatically move to the next video in the Classroom when playback concludes
- 1 ) Info about files for project
- 2 ) Intro to Video and look at end result reports
- 3 ) Overview of steps in building a Data Model in PowerPivot
- 4 ) Look at source data files including text files with 5 million rows of records
- 5 ) From Text Files, use Power Query to import 5 million rows of transactional sales data into the a Fact Table in the Data Model in PowerPivot
- 6 ) From Excel file, use Power Query to import Product Retail and Standard Cost Dimension Table into the Data Model in PowerPivot
- 7 ) Build Calendar Dimension Table in Excel and then use Power Query to import into the Data Model in PowerPivot
- 8 ) From web get ISO 3166-1 County Code Data to build and then use Power Query to import Country Code Dimension Table into the Data Model in PowerPivot
- 9 ) Build One-to-Many Relationships between Fact Table and Dimension Tables
- 10 ) Build DAX Calculated Column for Net Revenue for each transaction. See the DAX functions: RELATED (Like VLOOKUP in Excel) and ROUND.
- 11 ) Build DAX Measure (Calculated Field) for Total Net Revenue (Overall Total Net Revenue for entire Fact table). See the DAX function: SUM.
- 12 ) Build PivotTable to see that the relationships are working and that we can pull fields from Dimension Tables and Fact Tables.
- 13 ) Build PivotTable Report to show Net Revenue for each Country.
- 14 ) “Trouble Shooting” Part of Example: Tracking Down Error between web site data and company data, including finding error and updating Country Code Excel Table and refreshing the linked table in the …
- 15 ) Hide Fields from Field Lists using “Hide From Client Tools”
- 16 ) Build PivotTable to see that with a Data Model PivotTable you can NOT group Dates
- 17 ) Create DAX Calculated Column for Month Number and Month Name. See the DAX functions: MONTH and FORMAT (Like TEXT in Excel).
- 18 ) Build PivotTable to see that Month Name does NOT sort correctly in a Data Model PivotTable.
- 19 ) Learn how to Sort Month Name column by Month Number so that Month Name sorts correctly in a data Model PivotTable
- 20 ) Build relationship between Calendar Table and Fact Table
- 21 ) Build PivotTable Report to show Net Revenue by Month
- 22 ) Create DAX Calculated Column for Year. See the DAX function: YEAR.
- 23 ) Build PivotTable Report to show Net Revenue by Month & Year
- 24 ) Build DAX Calculated Column for COGS for each transaction. See the DAX functions: RELATED (Like VLOOKUP in Excel) and ROUND.
- 25 ) Build DAX Measure (Calculated Field) for Total COGS (Overall Total COGS for entire Fact table). See the DAX function: SUM.
- 26 ) Build DAX Measure (Calculated Field) for Gross Profit using Measures (Calculated Fields) in our DAX formula.
- 27 ) Build PivotTable Report to show Net Revenue, COGS and Gross Profit for each Year and Month.
- 28 ) Build PivotTable Report to show Percentage Change for Net Revenue over Same Period Last Year.
- 29 ) Build DAX Measure (Calculated Field) for Percentage Change over Same Period Last Year using the DAX functions: CALCULATE, SAMEPERIODLASTYEAR and IF.
- 30 ) Build PivotTable Report to show Percentage of Grand Total for Each Product. Concept behind the formula.
- 31 ) Build DAX Measure (Calculated Field) for Percentage of Grand Total using the DAX functions: CALCULATE and ALL.
- 32 ) Refresh Reports when source data changes. In our example we bring 7 million rows into the Excel PowerPivot Data Model.
- 33 ) Update Calendar table
- 34 ) Summary and Conclusion.
