Completed
) From Excel file, use Power Query to import Product Retail and Standard Cost Dimension Table into the Data Model in PowerPivot
Class Central Classrooms beta
YouTube playlists 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.
