Category Archives

49 Articles

Power BI Pro Tip: Show Top x results with RANKX() function

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!

Version compatibility between Power Pivot Data Models in Excel 2010 / 2013 and SharePoint 2010 / 2013

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?

 

 

Power BI Pro Tip: LOOKUPVALUE() function

Power Pivot is a great way to do data modelling and analysis right in Excel. It works great for data that is dimensionally organized (facts and dimensions) as well as other forms of data. It even enables you to define relationships between datasets regardless of source. However, one thing that has been hard is the following: consider the scenario where you have stock values for certain stocks for certain days, like below:

 

Let’s also assume you have a table that shows marketshare (or something else) per company on a certain date, like this:

Finally, you have a third table that lists the stock label by company, like so:

Now, assume that you would like to add the stock value of a company on a certain date next to the market share for that company at that date so as to provide more context to a potential relationship between market share and stock value. Maybe a bigger market share has an impact on stock value?

Naturally, what you would do is load these tables into Power Pivot so you get the following:

Now, the next step would be to add relationships between these two tables. The relationships should be defined as follows:

I.E.: Stock labels and Market shares are related on the Company column, whereas Stock and Stock Labels are related on the Stock / Stock label column.

We can now try to get the stock value for the company at a certain date, but how? Just using RELATED() to get stock values will not work as it will return a table. You could use MAX or MIN to then get a maximum or minimum value, but that is not what we are after: we wanted to return the stock value for that company at exact that date. More generally, this problem occurs when a table is related “twice” to another table, such as monthly targets by person vs. actuals (the relationship between the actual and target table is double: both on month as well as person).

The solution is using LOOKUPVALUE() and here is how. In the Marketshares table I add a calculated column with the following definition:

 

 

This might seem complex, so allow me to explain. What this does is the following:

Look up and return a value from

The Value column in the Stocks table (Stocks[Value])

For which

The stock label is equal to the stock label on record for the company (RELATED(‘Stock Labels'[Stock Label]))

And

The date equals the date of the market share information.

The result is:

Pretty nifty huh? Turns out that to use LOOKUPVALUE() this way you do not even have to be able to relate the lookup table to the data model at all. In my example the relationship between Stocks and Stock Labels is not even necessary, although I find it good practice to include all relationships just for clarity.

Power BI Pro Tip: DIVIDE() function

If you ever used Power Pivot to calculate things such as sales amount per capita or averages of some sort you will have run into the situation that the denominator (the column you want to divide by) is empty or zero. To cope with a potential division by zero, Power Pivot outputs Infinity. This can be seen in the screenshot below where the ‘Per Capita’ column is defined as:

Of course you can fix this by using

and others to work around the error. However, the DIVIDE() function makes this all a lot easier!

The DIVIDE() function takes two required and one optional parameters, which are: numerator, denominator and an optional value to return when division by zero occurs.

To see what DIVIDE() does, consider the following screenshot:

The Divide1 column here is defined as:

Whereas the Divide2 column contains the following function: 

 

The results are great, no Infinities are returned! By default DIVIDE() returns empty in case of a problem (Divide1 column). You can override this by specifying the third parameter so to return a fixed value in cased of a problem (Divide2 column in my example).

Hope this helps!

Power BI learning resources – follow up

For those of you that would like to get up to speed with Power BI but rather read a book, here are some suggestions:

  • For Dutch readers: Praktijkboek PowerPivot in Excel
  • DAX Formulas for PowerPivot by Rob Collie
  • Microsoft Excel 2013 Building Data Models with PowerPivot by Marco Russo and Alberto Ferrari

 

 

Power BI learning resources

Below is a list of learning resources for Power BI that I am aware of. I strongly believe in less reading, more doing with regards to Power BI, but to get a feeling of where to start and what the solution can and cannot do these might help:

Once you have looked at some (or all) of these just start using the tools and applying them to your situation! That’s the best way to learn. If I missed any resources, please let me know.

 

Nederlandse postcodes in Power Map (Support for Dutch postal codes in Power Map)

Normally I blog in English, but since this post is specifically about support Dutch postal codes in Power Map, this post will be in Dutch. For those interested, the answer is: Dutch postal codes are supported in Power Map, but you need to use the first four digits and strip off the last two characters. Better stil: use the address (if you have it).

Een veel gestelde vraag van mijn klanten is: is er ondersteuning in Power BI / Power Map (Geoflow) voor Nederlandse geografische informatie, zoals postcodes, steden en adressen? In deze post wil ik antwoord geven op deze vraag met de volgende dataset:

Met deze data kun je gemakkelijk een Power Map maken door in Excel op Insert / Invoegen te klikken en dan voor Map te kiezen. Als Power Map gestart is worden Address en City al automatisch op de kaart geplaatst. Klik op ‘Postal Code’ en kies ‘Zip’ om deze ook aan te zetten (zie onder):

Nu, als je Address aanklikt dan wordt de data perfect geplot op de gekozen locaties:

Dus: Nederlandse adressen werken prima.

Als we vervolgens overstappen naar City, dan ontstaat het volgende:

Opnieuw, klopt perfect.

Echter, als we nu ‘Postal Code’ kiezen dan krijgen we een lege kaart. Dit komt omdat Power Map op dit moment alleen de eerste vier getallen van een Nederlandse postcode kan ondersteunen en niet de volledige vier getallen plus twee karakters. Dus 1000 AA werkt niet, maar 1000 wel. Natuurlijk is dit minder specifiek dan 1000 AA. Als je dus de laatste twee karakters van de postcode afhaalt werkt de mapping weer:

Concluderend: Nederlandse geografische informatie wordt ondersteund en als je adressen hebt kun je zelfs heel specifiek plotten. Als je alleen postcodes hebt kun je op dit moment niet de volledige postcode gebruiken, maar alleen de eerste vier getallen. Overigens kun je met die vier getallen wel regions laten plotten op je kaart 🙂

The most awesome Power BI soundtrack

The most awesome Power BI soundtrack is of course I’ve Got The Power by Snap! 🙂
If you have a better idea, just let me know. Enjoy!

Analyzing Amsterdam Crime Rates with Power BI

Here is another example of what can be done with Power BI. This time I attempted to analyze crime rates in Amsterdam.

I retrieved burglary and burglary attempts figures from the Dutch Police website: http://www.politie.nl/misdaad-in-kaart/lijst?geoquery=amsterdam&categorie=1&categorie=2&pageSize=500&page=1

There are three pages here, so I use Power Query to get all three pages into Excel and then appended them together in one big table. I did this using three ‘from web’ commands in Power Query, so I got the following queries:

Next step was to append the three tables together. I started with the ‘append’ command in Power Query which allowed me to append Page1 and Page 2 together. A quick edit of the code gave me the result I was looking for:

And the resulting table:

Next, I loaded the table to PowerPivot, added a quick sum and then opened Power Map by going to InsertàMap in Excel.

In Power Map I created three layers, one using shapes, another one using a heat map and a third using a column graph. Then I created a tour using these layers. The resulting video is below.

As you can see, it is really easy to visualize geographical data using Power BI in Excel! Pretty cool huh?

Power BI Pro Tip: Troubleshooting Power BI Data Refresh

With Power BI you can create an Excel sheet based on your data and then get it refreshed so your report always has the latest information.

If you run into trouble with this, here is how to troubleshoot the data refresh.

  1. Firstly, make sure you use Power Pivot to connect to your data source and not Power Query as refresh with Power Query is currently not supported.
  2. When making the connection to your data source, just use a regular data connection to the SQL Server source and not through an OData feed.
  3. In Power Pivot, make sure to use the same data provider as your data source has been configured to use in the Power BI Admin Portal (i.e. Native SQL or OLEDB) and enter the server and database name exactly the same as the data source has been configured in your Power BI Admin panel. With “exactly” I mean also casing: so myServer is something else than MyServer. So the server and database name needs to be identical, including casing.
  4. Finish your workbook and upload it.
  5. Make sure the Database Management Gateway is running. Do this by checking the status in the Power BI Admin Portal and check under Data Management Gateways.
  6. Next in the Power BI Admin Portal, check that the data sources have correct settings for server name and database name. Also check that the credentials entered here have permissions to the data sources on the server and have been re-entered after the last time the DMG has been registered. There is no harm in re-entering them just to be sure. Also check that the connection provider here matches the one used in Power Pivot and again the server and database name need to be exactly the same as in your Power Pivot connection settings (see item #3 above).
  7. In the Power BI Admin Portal, check that the user you will be using to schedule the refresh has permissions to do so on every data source you would like to refresh. Do this by opening the settings for the data source in the Power BI Admin Portal and making sure the user is listed under ‘users and groups’.
  8. Plan the refresh on the file. When the schedule hits, your schedule should start and finish successfully.

 

%d bloggers like this: