WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 525 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

An overview of PowerPivot for Excel 2010
Part five of a six-part series of blogs

PowerPivot is a superb addition to Excel: it allows you to create pivot tables based on multiple tables taken from a variety of data sources, and its DAX language lets you report any statistic you want. This overview summarises how to get started with PowerPivot, and what it does.

1. Getting started with PowerPivot for Excel 2010
2. Stage 1 - Importing the tables we need
3. Stage 2 - Creating a data model
4. Stage 3 - Creating a Pivot Table
5. Stage 4 - creating measures using DAX (this blog)
6. Stage 5 - KPIs in PowerPivot for Excel

Posted by Andy Brown on 31 January 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Stage 4 - creating measures using DAX

My sole purpose of this part of the blog is to give an idea of what PowerPivot allows you to do.  It's not intended to be a full-blown tutorial.

In particular, I'm not showing how to create and use a calendar table to show year-to-date, period-to-date and other time-based aggregate data.

## Our example - showing the proportion of films made

Suppose we want to show not the number of films made, but the proportion:

 We want to show not this ... ... but this.

So for example Steven Spielberg made 3 films in the 1990s, comprising 60% of all of the films made in that decade.  Although there is a way to do this using standard Excel pivot tables, we'll create a measure.

## Creating the measure

To create our measure, right-click on the Films table and choose to add it:

Right-click to add the new measure.

You can now create the measure as follows:

1. Give the measure a sensible name (here we've called it Proportion).
2. Type in a formula which makes sense (see below for what this does).
3. Choose a number format (we'll display the number as a percentage).
4. Choose OK.

These numbered steps are shown below:

The steps to follow to create our measure.

## Explanation of the measure formula

You write measures using the DAX language, a variant of the MDX language used in Analysis Services.  Here's a sample:

=COUNTA(Films[FilmName]) /
CALCULATE(COUNTA(Films[FilmName]),ALL(Films[Director]))

This has two parts.  The first part just counts the number of films for the current pivot table cell (this is called the filter context):

A pivot table just showing =COUNTA(Films[FilmName]).  The number selected is 3, because the count of films made by Steven Spielberg in the 1990s was 3.

The second part counts the number of films made for all directors, for the current filter context:

This is what =CALCULATE(COUNTA(Films[FilmName]),ALL(Films[Director])) would show: the number of films made by all directors, for the given decade.

Dividing the first part of the formula by the second gives the proportion of films made by each director for any given decade:

The percentages must sum to 100%.

Learning DAX is where the fun starts!  If you're in the UK and you're interested by what you've seen, Wise Owl run a two-day PowerPivot course.

So now you're displaying the right measure, the last thing to show is how to create KPIs!

This blog has 0 threads