Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

YouTube

Basic Excel Business Analytics - Comprehensive PowerPivot, Data Model, DAX & Reporting Example

ExcelIsFun via YouTube

Overview

Prepare for a new career with $100 off Coursera Plus
Gear up for jobs in high-demand fields: data analytics, digital marketing, and more.
This course aims to teach learners how to use PowerPivot to import and manipulate large datasets, build data models, create reports, and troubleshoot errors. By the end of the course, students will be able to build one-to-many relationships between tables, create DAX calculated columns and measures, generate PivotTables, and analyze data effectively. The course employs a hands-on approach, guiding students through practical examples and demonstrations. It is designed for individuals interested in enhancing their Excel skills for business analytics, particularly those working with large datasets and looking to improve their reporting capabilities.

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..

Taught by

ExcelIsFun

Reviews

Start your review of Basic Excel Business Analytics - Comprehensive PowerPivot, Data Model, DAX & Reporting Example

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

Someone learning on their laptop while sitting on the floor.