THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alberto Ferrari

Dynamic Ranking with Excel and PowerPivot

Ranking is useful and, in our book, I and Marco provide a lot of information about how to perform ranking with PowerPivot. Nevertheless, there is an interesting scenario where ranking can be performed without complex DAX formulas, but with just some creative Excel usage. I would like to describe it here.

Let us start with some words about the scenario: we want to rank products based on sales in a year (e.g. 2002) and see how the top 10 of these products performed in the following or preceding years.

Let us start loading inside PowerPivot some tables from the AdventureWorksDW database: DimProduct, FactInternetSales and DimTime. We can then use a PivotTable to see sales in 2002 (using the slicer) and, leveraging the sorting option of the PivotTable, we sort by SalesAmount, putting the best performers on top.

image

Doing this, we have solved the first part of the scenario, i.e. we have identified which are the best products in 2002, yet what we want to do is to perform an analysis on how they performed during subsequent years. To do so, we must first assign a ranking number and/or category to each product, and then reload this table inside PowerPivot, so that we can use the ranking category as a new slicer in a new PivotTable.

There is no way to reload the result of a PivotTable inside PowerPivot directly. Nevertheless, by simply copying the result of the PivotTable inside an Excel Range, we can then format that range as a table and link it into Excel. In the following picture I did exactly this, using very simple Excel formulas to compute the rank (i.e. ROW() – 4) and the RankCategory (if rank is less than 10, then “BEST 10” else “OTHERS”):

image

Now, before we format the range as a table, we need to face a small issue: if we later decide to change the slicer to 2001 or to change in any way the filters, the number of products returned by the PivotTable will change, resulting in less or more rows, depending on the filter. Since we want to assign a rank to ALL of the products, we want the PivotTable to always return all the products, regardless of the filter. This can be easily accomplished using the PivotTable Options of Excel, asking to show even rows with no data:

image

Now, the PivotTable will always return all the products. We can complete our Excel range with the correct formulas, format is as a table and call it RankedProducts. Done it, we can use the Linked Table feature of PowerPivot and load it inside the Vertipaq engine:

image

The work is almost done. We still need to create a relationship between RankedProducts and DimProduct, as shown in the next figure:

image

Now everything is ready for our report: just create a new PivotTable, put the RankCategory on the slicer, the product name on rows, the year on columns and SalesAmount on values, the resulting report satisfies our initial request: products are filtered based on the ranking in 2002 but the values shown are pertinent to all the years available in the database:

image

The interesting part of this scenario is that now, if we want to change the ranking filter using a different selection for time (in the following example we have ranked based on December 2001), it is enough to use the Update All button in the PowerPivot tab of the Ribbon to reload the new RankedProducts table and get a complete different ranking. The very same technique can be used to create custom sets and many other interesting analysis, which is something I leave to your imagination. Sorriso

image

As you can see, all of this has been done without ever writing a single line of DAX. Does it mean that DAX is not useful? Not at all, using DAX we can get much more powerful reports, nevertheless this is some kind of real self service BI, that can be authored by an Excel Power User with some basic knowledge of PowerPivot and a creative mind.

This is just a very simple example of what PowerPivot can do for your reporting needs. If you want to touch the real Karma of PowerPivot, don’t miss the opportunity to follow one of the workshops I and Marco Russo are bringing all over Europe in the next few months.

You can find more info on www.powerpivotworkshop.com. Hope to see you  there!

Published Tuesday, January 11, 2011 9:00 AM by AlbertoFerrari

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tomislav Piasevoli said:

Excellent solution!

January 12, 2011 12:18 AM
 

Bob Phillips said:

Nice approach Alberto, but why did you sort the data then use =ROR()-4 to get the rank. Why not just use the RANK function (after all, that is what it is for), =RANK(R5,$R$5:$R$48,0)

If you are worried about duplicates, you can handle that too =RANK(R5,$R$5:$R$48,0)+COUNTIF($R$5:$R5,R5)-1

January 13, 2011 7:37 AM
 

AlbertoFerrari said:

Bob,

As strange as it might seems, I am not an Excel guru at all and... I have never used the RANK function :). Nice to learn about it, anyway, there are so many features of Excel I don't know.

The interesting part, and your comment highlights it perfectly, is that using two steps of evaluation, you can mix Excel analytical functions and PowerPivot engine to build complex models, where Excel performs some part of the computation and PowerPivot some others.

I am very excited about how users will leverage these capabilities to create reports in the near future.

January 13, 2011 7:49 AM
 

Alberto Ferrari said:

I spend most of my time developing complex BI solutions and, doing that, I always talk with DBA, developers,

January 24, 2011 5:51 AM
 

Eddy Nijs said:

Alberto

Interesting article. Another (expert) tip is the usage of a dynamic range. In Name Manager create a new range with formula =OFFSET($B$2;0;0;COUNT($B:$B),4). $B$2 must refer to the first column header of your pivot table. This way you do not need to repeat the columns Product Name and Total Sales Amount. When creating a Linked Table you can press F3 range selection window, you will get a list of named ranges, and select your newly created range. In some cases you are not able to change all the column name in the pivottable.

I prefer to have a more complex DAX formula this is less error prone (too fews formulas which refer to data in the pivottable).    

Nice trick of Bob.

February 10, 2011 10:44 AM
 

AlbertoFerrari said:

@Eddy,

Very nice feature, thank you for sharing, I never thought at this!

I prefer DAX too, but each method has pro and cons. Using DAX it would be quite difficult to make the ranking dynamic over the date. Challenging and interesting... but not easy at all. Maybe it will be a nice idea for a new post... I'll take it into account. :)

Moreover, DAX ranking is not very easy to compute and I think Information Workers will prefer to use this method that, even if a bit "strange", is very easy to implement and update based on their daily needs.

February 10, 2011 12:34 PM

Leave a Comment

(required) 
(required) 
Submit

About AlbertoFerrari

Alberto Ferrari is a Business Intelligence consultant. He his interests lie in two main areas: BI development lifecycle methodologies and performance tuning of ETL and SQL code. His main activities are with SSIS and SSAS for the banking, manufacturing and statistical sectors. He is also a speaker in international conferences like European PASS Conference and PASS Summit.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement