BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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:
- Give the measure a sensible name (here we've called it Proportion).
- Type in a formula which makes sense (see below for what this does).
- Choose a number format (we'll display the number as a percentage).
- 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:
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!