Data Analysis & Reporting using Power BI Training Course

• Converting data to Tables and Pivot Tables
• Top 10 functionality
• Copying and pasting pivot tables
• Manipulating data to show % of column, row and grand total
• Understanding a pivot table you never created
• Pivot Chart
• Building a dashboard in Excel from scratch

Case: Practical application 

• Introduction to Power Pivot
• When to use Power Pivot
• Importing data into Power Pivot directly from Excel
• Importing data into Power Pivot from data warehouses
• Importing data from Access, SQL, Oracle or SAP
• The data view vs the diagram view
• Building relationships between tables
• Relational Database
• One-to-many relationships
• Editing relationships
• Deleting relationships
• Building the first Pivot Table from Power Pivot
• The power of filtering context
• Retrieving data from different ranges and tables
• Introducing Slicers
• Formatting slicers
• Using slicers as control selection in building dashboards
• Multiple selections from slicers
• Producing the first report from Power Pivot

Case: practical application

• Import the data from Access
• Specifying the data to import using the data import wizard
• The Preview and filter option
• The Select Related Tables option
• Only select the tables related to a certain table
• Filter out certain columns
• Hide from Client Tools
• Data types
• Foreign Key vs Lock-Up Key
• How to create a relationship between two tables if there is no apparent key
• The Create Relationship Wizard
• How to detect relationship automatically
• DAX (Data Analysis Expressions)
• Calculated Columns vs Calculated Fields

Case: practical application

The purpose of this model is to allow the user to build a dynamic dashboard from scratch, comparing data across different products, regions, salesmen, or any other criteria for both actual and forecasted. The comparison will include YoY% (year on year % growth) of the last month vs same month last year, last quarter vs same quarter last year, year to date vs same period last year for both actual and budgeted data. In addition, the dashboard will be automatically reporting on the best 10 products with all their corresponding figures of growth YoY. The dashboard will include sparklines, pivotcharts and all other supporting tables that are dynamically changing with the selection of control options. In order to be able to create such a dynamic dashboard, extensive discussion on the following DAX functions will be examined: 

• Related
• Day, Mont, Year, Date
• Lastdate
• Dateadd
• Calculate
• All
• Left, Right
• Values
• Hasonevalue
• Format, Text
• Divide
• Isblank
• Not
• Totalytd
• Totalqtd
• Totalmtd

• Beware of the “Naked” Columns when writing measures
• Difference between referencing a column and a measure
• Assign the measures to the relevant table
• CALCULATE(), The basis of (almost) all the work in PowerPivot
• Syntax
• Example
• Another one
• Yet another Example
• Additional Alternatives
• The “ALL” Function (a.k.a. The “filterremover”)
• The ALL() Benefits
• 1.Percentage of Parent
• 2.Negating the Slicers
• Important
• ALLEXCEPT()
• ALLSELECTED()
• Working with multiple tables
• Lookup Tables and Data Tables
• Viewing the relationships in Diagram view
• CALCULATE() goes through relationships also
• INTERMISSION  
• Disconnected Tables
• Add the Parameters
• Add a Harvester Measure
• Have you consulted Field List?
• Parameters are friends of Rows and Columns
• Variations in Disconnected Tables
• Filter()
• Working rules
• Thresholds with disconnected tables
• Validation
• Threshold with min and max boundaries

• Time Intelligence
• Calendars
• How to create a calendar table
• What you need in a calendar
• Getting started
• Special Features of Calendar Table
• DATESYTD()
• Can the year-end date be changed?
• DATESMTD() and DATESQTD()
• TOTALYTD()
• FIRSTDATE() and LASTDATE()
• ENDOFMONTH(), STARTOFYEAR(), etc.
• CLOSINGBALANCEMONTH(), CLOSINGBALANCEYEAR(), etc.
• DATEADD()
• Growth Since Prior Year (YOY)
• Some Drawbacks of DATEADD()
• SAMEPERIODLASTYEAR()
• PARRALLELPERIOD()
• NEXTMONTH(), PREVIOUSYEAR(), etc.
• DATESBETWEEN()
• Life To Date()

Case: practical example

• Using powerful DAX functions that will facilitate lots of dynamic modelling
• IF()
• BLANK()
• DIVIDE()
• ISBLANK()
• HASONEVALUE()
• More IF()
• VALUES()
• SWITCH()
• SWITCH TRUE()
• The “X” Functions (also called “Iterator” Functions)
• SUMX()
• MINX(), MAXX() and AVERAGEX()
• FILTER()
• COUNTX() and COUNTAX()
• “X” Functions work with the “absentee” also
• RANKX()
• TOPN()
• Multiple Data Tables
• Merging two data tables into one Pivot
• “Hybrid” Measures
• Few Points to remember
• Data Tables with different “Granularity”
• Budget vs. Actuals Case: putting it together

• How to Install
• Getting started
• Creating the most amazing and dynamic dashboards
• Very valuable visualizations
• Changing the controls of your different visualizations
• Controlling the whole dashboard or page by page
• Downloading new visualizations from Microsoft
• What if analysis
• Different dashboards for different users
• Cross referencing
• Drill in and drill out
• Hierarchy
• Map showing your biggest sales region
• Going through the performance of your products one by one
• A pyramid of all your costs
• Your users can get charts and data using Q&A just like Google
• Get data from literally any source
• Work on the tables, just like PowerPivot
• Manage Relationships
• New Measure
• Calculated Columns can also be created in the same way, by right-clicking in the Field list
• Creating Reports
• Import already prepared PowerPivot Models
• Publishing your dashboards to cellphones, tablets and desktops
• Dashboard, amazing dynamic examples
• Sharing the dashboard in various ways

Case: practical case in power BI