PowerPivot Inside Out (Part 2/3)

This is the second article of three. If you hadn’t read the first part yet, it is recommended that you do so. It talked about the basics of PowerPivot and already covered the first three of the ten things you need to know. So we will continue with number four…
 

4.      Sorting values of one column by values in another

In Excel this is more or less science fiction. But in PowerPivot this works beautifully. So imagine this: You have a column with descriptions that go something like “beginner”, “intermediate” and “master”. When you put these in a pivot table, you actually get a right order, but only because that is the alphabetical order of the descriptions.

But what if our descriptions were “Rookie”, “Seems to know his way around” and “Almost god”. If you were to insert these descriptions into a pivot table, the order of things would be anything but natural. This is a very rudimentary example, but it can go much further.

In PowerPivot, time intelligence is a big thing; you can read more about it in the section below entitled Time Intelligence. In order for many of the brilliant time intelligence functions you can use to work, you have to use what’s called a Calendar Table. It’s a simple table with continuous dates. So before long you will have to deal with dates in PowerPivot. When it comes to dates, pivot tables created from a Power Pivot data model do not act as one would expect. For example if you add dates to the Rows and right click on those dates…

…there is no ability to Group or Ungroup them. So if you want to get data by months, you have to get month names to a separate column. Nothing wrong with that, you either use Text or Format, but when you add those to a Pivot table, you get something like this

So the months are not in the logical order. The alphabetical order of the months is no surprise, but it is a hassle. Luckily we have a way to solve this. All you need is a column in the PowerPivot table with numbers that correspond to the correct sorting order. With months this is a simple MONTH function.

Now here it is. In PowerPivot you have a Sort By Column command. And here you can say

Sort the Month Names as you would Month Numbers. And right away we get

If you want to read more about this you can do so in this article.
 

5.      Disconnected tables

Usually in a PowerPivot Data Model, you would see something like this…

…so a Fact Table and Dimensions as connected tables. This is a very simple star schema as opposed to a far more complex snowflake schema where the dimensions have their own connected tables and so on. These connections are part of the magic behind PowerPivot. But sometimes you want to have a table that is not connected to any other table. We call this tables disconnected tables. They may sometimes cause havoc, as they almost inevitably lead to the “Relationships between tables may be needed” warnings, but they can be a great asset.

Here’s an example. Our data will be as simple as this

Now based on this data, we have a following analysis.

So SUM Of Sales per Year per QTR. This is a very simple measure
But now we want to take this a step further; we want to calculate a SumOfSales for our top 10 sales in that period. Again a fairly simple measure Sales – TOP 10…

Now here comes the tricky part. What if we want to change that 10 dynamically, showing the Top 3, 20, 50… Here we will create a new table with the values we want to use for the N.

We add this table to our data model…

And since we will not add a connection, this is now a disconnected table in our Data Model.

Now in the Pivot table we add a Slicer based on the TOPN field of the disconnected table.

Next step is to write a measure, which will return the selected value. This can be a simple MAX like so…

With this we can modify the TOP10 measure we wrote earlier, to include this selection…

With this measure, we can now use the slicer to modify the N counter and get a SumOfTopNSales. On the image below is the Sum of top 12 sales.

It’s ingenious.
 

6.      Time Intelligence

As mentioned in section 4 above, Time Intelligence is a big thing in PowerPivot and DAX. It can do wonders for you, but one condition has to be met for you to be able to use Time Intelligence. You must have a Calendar Table! 99% of Data Models include a Calendar table. It’s a table of sequential dates (none can be missing!). Now you connect this Calendar Dimension to your Fact Table Date field and you are set to go! Now you can use functions like TOTALYTD, TOTALMTD, TOTALQTD…

As you can see, TOTALYTD expects as a second argument something called Dates. This is the Date field of your Calendar Table. As you can see it also offers you a [YearEndDate] option, where you can specify something different from 12/31/ and get YTD for a July – June year and so on.

Another great function is a DateAdd where you can say do a sum of sales, but go back 8 months or back 4 years and so on.

The 8 months back would be
This is a perfect FILTER function to use in your CALCULATE, but I’m getting ahead of myself. The goal here is to let you know that Time Intelligence is a big thing and deserves to be among the 10 things you need to know and use if you want to take your PowerPivot reports to the next level.

Now we are three steps closer to eternal happiness :)