Big Data Analytics

Duration: 2 days
Workshops:  17-18 Mar 2018
            15-16 Sep 2018

In this course, you will learn to import data from a variety of sources, and summarize data quickly and effectively in Power Pivot to deliver KPI report in minutes. You will also learn to use DAX formula language, time-intelligence functions and cube formulas to analyse data.

Key Modules Covered in this Course

Tour of the Power Pivot

  • Why Power Pivot is great for accountant
  • Import million rows data into Power Pivot
  • Learn how to create linked table
  • Consolidate variety Data Tables with Power Pivot
  • Build Relationship for data analysis
  • Add Slicer to a Power Pivot Report

Use DAX (Data Analysis Expressions) formula to analyse data

  • Construct efficient databases and designs for Power Pivot analysis
  • Understand Power Pivot formula engine
  • Use DAX formula to create calculated columns
  • Use calculated columns as new filterable items for analysis
  • Build “or” and “and” relation in DAX formula
  • Compare Measure with Calculated Columns
  • Hide Power Pivot fields from Pivot table

Advanced DAX functions

  • Introduce the dramatic benefits of “Portable Formulas”
  • Use one Measure inside another Measure
  • Learn to use functions that filters before it CALCULATEs
  • Learn to use functions that calculate data which ignore ALL filters
  • Use disconnected tables for sophisticated data analysis

Time intelligence with DAX

  • Introduce Time Intelligence and Calendar Tables
  • Calculate current month, current quarter, year to date (YTD) information
  • Compare values from various time periods

Case Study: KPI variance report

  • Build the basic measures for your report
  • Protect time intelligence functions from discontinuous dates
  • Use unlinked tables to toggle thousands/millions
  • Use unlinked tables to toggle between sales, costs and profits
  • Highlight key performance indicators with KPI icons

Combine PivotTable Power with Formula Flexibility

  • Convert Power Pivot PivotTables to CUBE formulas
  • Extract a Slicer selection with CUBERANKEDMEMBER
  • Evaluate a measure for multiple members with CUBESET


  1. Terry老师的课程有许多数据模型建立的实战经验,课程内容实用,结构合理,解答问题很有耐心,每次上课都收获颇多,学过的技能马上就用在了工作中。

  2. The course is great, it has open up the limitations to analysis data in different angle simultaneously and make analysis more interesting.

    Though it require more practice to master the use, but enough for me to think various way of analysis the data.

  3. 学建模后感



    当然,这种意义也是慢慢生长,先是对一小撮人有意义,再可能是更多的人。总之,知道别人不知道的,能做别人不能做的,就让大家在学习和追赶中,慢慢升级迭代,build block越做越好。


  4. Great course, Great content and Great tutorial as always. The three BI courses are setting up competitive advantage for me in the real world. Have attended most of Terry’s courses and none of them disappoint. Enriched contents and Terry always can change the scenarios to resolve our queries on site. So Well-Done!

  5. This is a amazing course. it is easily to find a loads of videos or tutorials online regarding to Power Pivot. However, Terry is the only one teaches the logic and secrete to successfully use PowerPivot. Highly reconmmed!

  6. Very impressive course.

    The calendar table, measures and DAX formula are so powerful. Building block idea is so great!

    Learned a lot of things this weekend. Thank you so much, Terry

    This is a course you must have!

  7. 这次课程又学到了新的思路和新的公式,回去以后需要时间消化一下。希望自己不要在懒惰,可以花些时间把它运用在工作中。期待下次学习几个BI软件的交叉运用,融合在一起来解决问题。谢谢老师上课时的耐心讲解,人品大大的赞!

Leave a Reply