ALL(), ALLEXCEPT()

ALL(), ALLEXCEPT() The ALL function is the opposite of the filter options in that it forces a Pivot Table to ignore the filters placed on it by where it is…

COUNTROWS

COUNTROWS Another useful function is the COUNTROWS function which at its simplest allows you to count the number of rows in a table. So where DISTINCTCOUNT only counts the unique…

DISTINCTCOUNT

DISTINCTCOUNT With traditional Pivot Tables one of the weaknesses was its inability to COUNT the number of unique items in a list. It could summarise by them and then you could…

CALCULATE function in PowerPivot

CALCULATE function in PowerPivot A function you will be using very often is the CALCULATE function. The CALCULATE function is the equivalent of the SUMIFS, COUNTIFS, and AVERAGEIFS functions in…

PowerPivot SUM and AVERAGE functions

PowerPivot SUM and AVERAGE functions The ability to SUM and AVERAGE should be the easiest place to start. If I wanted to create a SUM of a particular column I…

Creating DAX Measures

Creating DAX Measures When you are in the Excel file on a sheet with your Pivot Table you can insert a new measure into the Pivot Table. You do this…

Understanding DAX Measures with an example

Understanding DAX Measures with an example A good way to understand how DAX Measures work is to look at an example. The process is best described as: 1.     Excel looks…

Relationship Exercise

Relationship Exercise We now want to create some relationships between the tables so that we can perform some tests and get better reports. Open the file you created during the…

PowerPivot Relationships

PowerPivot Relationships In traditional excel we often needed to combine tables to make them more meaningful e.g. convert a client number into a client name. Depending on your background you…

Calculated Column Exercise

Calculated Column Exercise As mentioned previously, we want to create a relationship between our tables and run a few tests to see if we are being billed the correct amounts.…

Calculated Columns in PowerPivot

Calculated Columns in PowerPivot Calculated columns and measures are very similar concepts in that both of them allow you to work within and across the tables to create new information…

Build your first VLOOKUP

Build your first VLOOKUP The easiest way to begin is to just have you create your own VLOOKUP. Download and save the file below to follow along with the exercises.…

VLOOKUP explained in simple terms

VLOOKUP explained in simple terms In the following lesson we cover the topic of VLOOKUP explained in simple terms. The first place to start is what VLOOKUP is trying to achieve…

PowerPivot Import Data Exercise

PowerPivot Import Data Exercise The files required for this exercise were downloaded at the beginning of the course. All the files you will be working with are in the folder…

PowerPivot Pivot Table- the same but different

PowerPivot Pivot Table- the same but different The Pivot tables created out of PowerPivot are slightly different from the traditional pivot tables. Watch the video clip or read the extracts…

PowerPivot Date Table

PowerPivot Date Table In normal Excel and Pivots you have some options if Excel recognized that a column contained dates. So for example you can group a date column in…

Datastream into PowerPivot

Datastream into PowerPivot Microsoft has provided a Data Market where you can buy (sometimes for free) data. Things like the temperature every day for the last xx years in different…

Databases into PowerPivot

Databases into PowerPivot You can also link directly to common forms of databases. Watch the video clip or read the text below

YouTube

[tabby title="If YouTube doesn't work, click…

Text files in PowerPivot

Text files in PowerPivot PowerPivot can import data from multiple type of text files. Watch the video clip or read the text below

YouTube

[tabby title="If YouTube doesn't work,…

Unlinked Excel file in PowerPivot

Unlinked Excel file in PowerPivot You can import data from an Excel file that is NOT the Excel file through which you opened the PowerPivot window. NOTE: This is if…
It is possible…