Welcome to 2013!
Hi folks, welcome back 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…
“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:
Now I want to create the top 10 by clicking on Row labels:
And I select Top 10 EnglishProductname by Sum of Sales
This will give me the top 10 sales by EnglishProductname, now we can order it from A to Z to get the ordered results:
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:
Next for the rank measure I wrote the following:
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.
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:
And add the filter:
Resulting in the final top 10 view in Power View
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:
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:
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
([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:
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:
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