Guest Post from Kasper! Excel 2013, Power View, top 10, “long tail” and how DAX helps

Welcome to 2013!

Hi folks, welcome back Smile  Over the holidays, Kasper submitted a post – yes, THAT Kasper.  Appropriately for the year 2013, it is focused on Excel 2013.  Not many people have 2013 on their desktops yet (even me really – I just have one “test” laptop running it), but over the course of this year I’ll be slowing “rotating” Excel 2013 topics onto the blog.

Anyway, Kasper and I decided to “hold” his post until today so that everyone sees it. 

Over to Kasper…

image

“I’ll be back” – Kasper de Jonge

Ok its been a while since I blogged an actual scenario here on PowerPivotPro but here is another one :) . Its that time at the end of the year and folks here at Microsoft are out enjoying their vacation so lots of meetings get cancelled,  this gives me the opportunity to do one of the the things I love, helping users of our products get the solution they need and write some blog posts :).

A few days ago I met a internal user who had 3 million rows of occurrences, products and dates in a SQL database and wanted to get some insights out of it, preferably in a highly visual output. We are fortunate here at Microsoft that we always get to play with the latest bits, so we have access to Excel 2013 that includes Power View.

In this blog post we will look at how we can show a top 10 list of best selling products in Power View and how we can solve a long tail problem that will allow us to visualize only the top best selling products in a chart and ignore the rest. I know these things are pretty straightforward in Excel (if you know where to find it) but it needs the help of DAX in Power View.

I am using adventureworks to simulate the customers scenario where I imported DimDate, DimProduct, DimSalesTerritory and FactInternetSales into PowerPivot. To get started I created a simple measure called:

Sum of Sales:=Sum(FactInternetSales[SalesAmount])

I then created a Pivottable in Excel to show the sales by product for a year in Excel:

image

Now I want to create the top 10 by clicking on Row labels:

image

And I select Top 10 EnglishProductname by Sum of Sales

image

This will give me the top 10 sales by EnglishProductname, now we can order it from A to Z to get the ordered results:

image

Ok now I want to get the same result in Power View. As you might know in Excel 2013 we have Power View baked into Excel. To get the same top 10 sales we have to do a manual calculation as Power View doesn’t have a “top x” filter like Excel has. We can solve this with a simple DAX expression that allows us to do a RANK on EnglishProductname by Sales and then filter this Rank in Power View.

I start by creating a Power View sheet and recreate the table:

image

Next for the rank measure I wrote the following:

Product Rank:=
RANKX(all(DimProduct[EnglishProductName]), [sum of Sales])

So what will this measure do? It will rank the [sum of Sales] for each cell in the pivottable against the [sum of Sales] for the table that is specified in the first argument, in this case all(DimProduct[EnglishProductName]). This will result in the RANK the value of  [sum of Sales] for the current row against all other EnglishProductName’s.

image

This is exactly the same function that Excel uses to determine its “top x”. Same as in Excel this function will only work if you have EnglishProductName on rows.

Now add the rank to the pivottable:

image

And add the filter:

image

Resulting in the final top 10 view in Power View

image

 

One thing to notice here is that I did not write my function as:

Product Rank:=RANKX(all(DimProduct), [sum of Sales])

which is usually best practice when using ALL(), ALL of the table will work when selecting any column from this table in the field list.  The problem lies in the fact that EnglishProductName is not the lowest granularity in the table. As it turns out the table is a slowly changing dimension which means there are multiple products with the same name but a different ID.

This happens when I add the ProductKey to the pivottable, as you can see we have two products with the same name but different keys:

image

This would give very weird rank results as the first argument of rank would iterate over a table (all the rows in the product table) that is different than the actual rows in the Pivottable (unique productnames). I hope that explains why I choose to not include the table but a specific column.

Now as you can imagine we have a lot of products and I want to see the distribution of sales over the products. Visualizing data is hard with all these products:

image

 

But as you might notice we do have a  long tail, meaning that we have a lot of products that will not contribute to most of the sales. What I want to do is see a chart with only the products that produce more that 2% or more of the total contribution. I don’t know how to easily do this in Excel, but I do know how to easily do this using a DAX expression :). I first create a expression that calculates the percentage of sales against all other products

Percentage sales:=
([sum of Sales] / CALCULATE([sum of Sales],ALL(DimProduct))) *100

This measure will calculate the sales of a single product against sales of all the products. Now we create a second measure that only returns a value when the value falls in the 2% of total contribution bucket:

top 2 percentage sales:=
IF([percentage sales] > 2,[sum of Sales], BLANK())

For the else clause of the IF(), a blank value returned by DAX will omit the row in the table in both Power View and Excel.

Adding this to the chart:

image

The cool thing here is that when I change the slicers the top 2% will change accordingly so its fully dynamic.

For example when I change the chart into a map you will see the top 2% per country not overall:

image

 

I hope this blog post has given you more insights on some of the capabilities that Excel 2013 with the Data model, Power View and DAX can give you. You can download the workbook here: https://skydrive.live.com/redir.aspx?resid=7F4E0559CC74581A!1006

5 Responses to Guest Post from Kasper! Excel 2013, Power View, top 10, “long tail” and how DAX helps

  1. Siraj Samsudeen says:

    >>What I want to do is see a chart with only the products that produce more that 2% or more of the total contribution. I don’t know how to easily do this in Excel
    Hi Kasper, this is easy to do in Excel. You need to create a measure for Percentage Sales. And then you can go to the same value filter area which you have used for filtering Top 10. Once there, select the measure Percentage Sales in the first drop-downs which lists all measures in the same table and enter the percentage in the 3rd drop-down. I have to use DAX magic but still easy enough in Excel.

  2. Jason Thomas says:

    Nice read, thanks for sharing!

  3. Vincent says:

    Nice write up! This post makes a great use case for purchasing Excel 2013.

  4. Sheraz says:

    Let’s say if Top 50% Sales were to be calculated while considering the following percentage distribution

    Product 1 10%
    Product 2 20%
    Product 3 30%
    Product 4 40%

    Replacing 2 with 50 in the formula mentioned above as

    Top 50 percentage sales:= IF([percentage sales] > 50,[sum of Sales], BLANK())

    would give products with sales > 50% which while considering the above distribution gives no results at all. In other words, it’s to filter products with sales > 50% rather than the top 50%
    At least this is how it looks like to me :) Maybe I’m mistaken big time :)

    I’m expecting Product 4 and Product 3 as they are the first 2 contributions to give > 50%

    Kindly advise and Thanks for your consideration

  5. Sampurna Mukhopadhyay says:

    One big problem that I faced when using the same situation is that I could not include other fields from the products table (Say product #,Active Date,) into the view. It is disrupting the rank.

Leave a Comment or Question