Dutch Data Dude
Data Nerdiness
Just a quick Power BI Pro Tip this time: if you use linked tables to add data to your data model in Excel, before you press the ‘Add To Data Model’ button be sure to go to the table properties in Excel and give your table a better name (better than Table X). This makes figuring out which data you are looking at so much easier. You will thank me later J
One of the more frequent scenarios is listing the top X results, such as most profitable products, biggest customers, top 10 best selling stores, etc. Also doing a top X selection helps reduce clutter in charts: a lot of data points can work as noise and obscure the data points that really matter and make the biggest impact.
In this post I describe an approach to implementing these scenarios using Power Pivot’s RANKX() function.
Let’s start with a simple dataset consisting of products (P1…P20 in my sample), Cities, Sales Amount and Number of products sold:
After adding this table to the Power Pivot data model, we can use the RANKX() function to get the best selling products / cities etc. I added the following measures to my table:
Sum of Sales Amount:=SUM([Sales Amount])
Sum of Number Sold:=SUM([Number Sold])
Rank of products by sales amount:=RANKX(ALL(Sales[Product]);[Sum of Sales Amount])
Rank of city by number sold:=RANKX(ALL(Sales[City]);[Sum of Number Sold])
These measures allow me to determine the top selling products by sales amount and best cities by number of products sold.
Only thing left to do is to use a Pivot Table / Pivot Graph or Power View / Power Map visualization and display the results.
If you create a new Pivot Table and add the Product column and the ‘Rank of product by sales amount’ measure you get the following:
So how do we get the top 10 selling products by sales amount is a nice ordered fashion? Very easy, just a matter of the right sorting and filtering. Click on the little downwards pointing triangle button at Row Labels and choose ‘More Sort Options’. There I chose Ascending and then selected the rank measure:
Now the Pivot Table is sorted by rank with the highest ranking product at the top. Now, to filter out only the top ten, we press the same button again and choose Value Filters and then Top 10. Here I made the following selections:
This seems maybe a bit counter intuitive, but what this does is return the lowest ten ranks (which would be 1 to 10 or the highest ranking products). Alternatively I could have used a Lower Than or Equal To Value Filter with these settings to produce the same result:
And here it is: a top 10 of products by sales amount.
Of course, you can also use Power View or Power Map to visualize these results. Here is a Power View based on the same information:
The trick here is to create the visualization just as normal (as above). Above displays the sales amount by product and the number sold by city. However, the catch here is that both the graph as well as the map have a filter on them that utilizes the rank measures I created. Here is the filter for the chart. The ‘Rank of products by sales amount’ measure is filtered to showing only values less than or equal to 10, i.e. the top 10.
What’s best about this is that it is very easy to change from top 10 to top 15 to top 5 or anything you desire. Also, the Power View is fully interactive. For example, clicking on one of the cities on the right shows which products are sold in that city. Note that it does not show the top 10 products in that city however.
Hope you liked this Power BI Pro Tip!
If you want to keep track of what is happening on Office 365 and what updates are rolling out and coming down the line check this site:
http://office.microsoft.com/en-us/products/office-365-roadmap-FX104343353.aspx
Trust me, a lot is happening on Office 365!
I have been getting a lot of questions on compatibility around Excel 2010 / Excel 2013 / SharePoint 2010 / SharePoint 2013. To be honest, I have been confused myself.
I encourage you to check out our Excel help page, which makes it crystal clear:
Note to SharePoint Server 2010 customers: Client applications used to create a Data Model need to align with the server applications that host a Data Model. For SharePoint Server 2010, this means you’ll need to continue to use Excel 2010 and a Power Pivot for Excel add-in to create and maintain a Data Model. Excel 2013 cannot be used to create Data Models that run on SharePoint Server 2010.
This means that you can upload an Excel 2013 file with a Power Pivot data model in it to SharePoint. However, if you interact with it (click refresh, click a slicer, etc) you will get an error message.
It does not get any more specific than that, right?
I get myself in many discussions about SharePoint as application platform for public / customer facing sites. The reason I end up in the discussion is because people are looking to use our great BI tooling to provide insight to their customers.
Most people do not believe that SharePoint is used to create internet sites (most people just know SharePoint as a collaboration platform).
Here is a great site that gives you an idea of what websites were created with SharePoint and it even features a great BI visualization of the data (PivotViewer). I encourage you to go check it out!
http://www.spsdemo.com/livepivot
My personal favorite is http://www.ferrari.com. Can you believe it has been built using SharePoint?
