CRPBI Certificate in Data Analysis & Reporting Using Power BI

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

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 SAB

The data view vs the diagram view

Building relationships between tables

Relational Database

One-to-many relationships

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

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.

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 “filter-remover”)

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()

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

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