Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Dive into a comprehensive tutorial on PowerPivot, Data Model, DAX, and reporting in Excel. Learn to import large datasets, build a robust Data Model, and create insightful reports. Start by importing 5 million rows of transactional sales data into PowerPivot, then create dimension tables for products, calendar, and country codes. Establish one-to-many relationships between fact and dimension tables, and use DAX functions to create calculated columns and measures. Build various PivotTable reports to analyze net revenue, COGS, and gross profit by country, month, and year. Explore advanced concepts like percentage change over the same period last year and percentage of grand total for each product. Troubleshoot data discrepancies and learn to refresh reports when source data changes. By the end, gain the skills to handle up to 7 million rows in an Excel PowerPivot Data Model and create powerful business analytics reports.

Syllabus

) Info about files for project.
) Intro to Video and look at end result reports.
) Overview of steps in building a Data Model in PowerPivot.
) Look at source data files including text files with 5 million rows of records.
) 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.
) From Excel file, use Power Query to import Product Retail and Standard Cost Dimension Table into the Data Model in PowerPivot.
) Build Calendar Dimension Table in Excel and then use Power Query to import into the Data Model in PowerPivot.
) 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.
) Build One-to-Many Relationships between Fact Table and Dimension Tables.
) Build DAX Calculated Column for Net Revenue for each transaction. See the DAX functions: RELATED (Like VLOOKUP in Excel) and ROUND..
) Build DAX Measure (Calculated Field) for Total Net Revenue (Overall Total Net Revenue for entire Fact table). See the DAX function: SUM..
) Build PivotTable to see that the relationships are working and that we can pull fields from Dimension Tables and Fact Tables..
) Build PivotTable Report to show Net Revenue for each Country..
) “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 Data Model so that the PivotTable report updates and has correct results..
) Hide Fields from Field Lists using “Hide From Client Tools”.
) Build PivotTable to see that with a Data Model PivotTable you can NOT group Dates.
) Create DAX Calculated Column for Month Number and Month Name. See the DAX functions: MONTH and FORMAT (Like TEXT in Excel)..
) Build PivotTable to see that Month Name does NOT sort correctly in a Data Model PivotTable..
) Learn how to Sort Month Name column by Month Number so that Month Name sorts correctly in a data Model PivotTable.
) Build relationship between Calendar Table and Fact Table.
) Build PivotTable Report to show Net Revenue by Month.
) Create DAX Calculated Column for Year. See the DAX function: YEAR..
) Build PivotTable Report to show Net Revenue by Month & Year.
) Build DAX Calculated Column for COGS for each transaction. See the DAX functions: RELATED (Like VLOOKUP in Excel) and ROUND..
) Build DAX Measure (Calculated Field) for Total COGS (Overall Total COGS for entire Fact table). See the DAX function: SUM..
) Build DAX Measure (Calculated Field) for Gross Profit using Measures (Calculated Fields) in our DAX formula..
) Build PivotTable Report to show Net Revenue, COGS and Gross Profit for each Year and Month..
) Build PivotTable Report to show Percentage Change for Net Revenue over Same Period Last Year..
) Build DAX Measure (Calculated Field) for Percentage Change over Same Period Last Year using the DAX functions: CALCULATE, SAMEPERIODLASTYEAR and IF..
) Build PivotTable Report to show Percentage of Grand Total for Each Product. Concept behind the formula..
) Build DAX Measure (Calculated Field) for Percentage of Grand Total using the DAX functions: CALCULATE and ALL..
) Refresh Reports when source data changes. In our example we bring 7 million rows into the Excel PowerPivot Data Model..
) Update Calendar table.
) Summary and Conclusion..