Year: 2016

Hex Tile Grid Maps for Power BI

Hex Tile Grid Maps for Power BI

April 21, 2016

I have always been fascinated by maps as a child, and could spend endless hours looking at the globe my parents got me as a present for my 5th birthday. I was so hooked on to it that my parents even considered removing it from my room fearing that it could hamper my social development (and this was in spite of my  parents being extremely proud that I could tell most of the countries and their capitals around that time!). Even though maps don’t intrigue me to that level anymore, I still follow them as part of my job and have written quite a number of blogs on getting spatial information in the Microsoft stack, starting from SSRS 2008 R2. So it was kind of natural that when I saw a couple of hex grid maps floating around my twitter feed a couple of months ago, I thought of reproducing it in Power BI as I knew it could be done.

Hex tile grid maps for Power BI

First of all, let us start with an introduction of hex maps and why they could be useful. Regular choropleth map is a tried and tested visualization for area maps but it carries the risk of under-representing some areas. For e.g., in a regular choropleth map of the US, DC is hardly visible along with some other North-Eastern states. A hex tile map solves this issue by giving each state equal weight. However, it comes with it’s own set of problems like balancing between depicting unique geographical features (like Texas and Florida being the southern most part of the country) versus depicting bordering states accurately. Because of this reason, you will find more than one version of hex grid maps and it is perfectly ok to choose the one that suits your need more appropriately. Now you can follow the steps below to reproduce a hex tile grid map in Power BI (and don’t forget to check out the Power BI report that I made with this technique at the bottom of my post):-

1) Choose a version of the hex tile grid map that you like from the internet. Or you can even make one easily in PowerPoint or any other image processing software (as it is just a collection of hexagons) based on the image that you get from the internet and save it as an image.

Making hexagons in powerpoint

2) Go to http://synoptic.design/ and upload the image to the synoptic designer by dragging and dropping the image to the designer.

Uploading to Synoptic designer

3) Ensure that the second icon on the bottom left is enabled (which helps us to automatically discover new areas). Now you can just click on the hexagons and the synoptic designer automatically discovers the areas for you, which is super cool.

Using the automatic discovery of areas icon in Synoptic designer

Now, for most people, this should be more than enough and the results come out really good. In my case, I decided to take a step further as I was planning to share the file for the community. If you notice carefully, you can see that more than 6 vertices are being plotted by the designer automatically (check out the multiple vertices in the section I highlighted).

Multiple vertices being recorded

To avoid this, I just wrote a bunch of formulas which would calculate the vertices in plain old excel and then just copied the 6 pairs for each of the 51 states manually.

Replacing it with just 6 vertices

Make sure that you map the areas to the appropriate state name / code also.

4) After this, your map is ready and you can just export it to Power BI, which would save the map data as a SVG file in your computer.

Export as SVG file for Power BI use

I would also request that if you make some interesting maps / shapes, please consider submitting it to the gallery so that other community members can also reuse it. I have submitted my map to the gallery and hopefully it will be approved by the SQLBI team (who created and still supports this wonderful tool).

5) Now open Power BI, and download the Synoptic Panel from here (if you don’t already have it) and import to Power BI. Once you have done that, click on the Synoptic Panel to add it to Power BI, and add the state code (which is the filed we are going to bind our dataset with the map) and a measure (like Total Votes) to it. Then click on the “Select Map” icon.

Synoptic Panel in Power BI

Then browse to the SVG file we just downloaded from Synoptic Designer and you should have your basic version of the hex tile map ready. Feel free to experiment by adding measure values to the Saturation or State values.

Basic hex tile grid map in Power BI

Now as a reward for making it till here, I thought of letting you play with this simple report that I created using this hex map. In this report, you can select any year from 1916 and see the winning party of each states (I only included the data for Republican and Democratic parties), as well as the nominees of the election. You can also see the number of electoral votes they won along with the popular vote %, which gives some pretty insights. For e.g., it is interesting to see that George W Bush won the election even though he got fewer popular votes than Al Gore in 2000. Click on the Expand icon to see the report in full screen.

Posted by SQLJason in Power BI, Spatial / Map Reports, 8 comments
NBA style Shot Charts in Power BI

NBA style Shot Charts in Power BI

February 11, 2016

Recently, I created a NBA shot chart in Power BI as part of my entry to the Microsoft Power BI Best report contest and I had got a lot of questions on how I made the visual. So I decided to write a quick post on how I made the shot chart as well as use this opportunity to present my entry, which got selected as one of the Top 10 finalists in the contest.

Note

My interactive contest entry is embedded in this post (thanks to the new Publish to Web feature in Power BI) and a full screen version of the same report can be obtained from here.

My entry is an analysis of the first 35 games played by Stephen Curry from the Golden State Warriors in NBA. The main feature of the entry is a Shot chart which shows the position from which he attempted his shots and the color denotes whether he made or missed it.

1 Shot Chart

To make a similar shot chart, follow the steps below:-

1) The most important part of any report is getting the data. I had a few sources for my data (www.nbastats.comwww.nbasavant.comwww.datavizdoneright.com) from where I directly got my (X,Y) position data. But if you are really serious, you might want to look at the following blog posts which show how to scrape data directly –

How to create NBA Shot Charts in R

How to create NBA Shot charts in Python

2) Once you get your (X,Y) location data, you can import the results into Power BI and then create a scatter chart from the data.

2 Scatter chart

3) Get a background image for the basketball court (I used one that I found from  www.datavizdoneright.com since it had the logo of Golden State Warriors and looked nice). Now you can import the image and place it behind the scatter chart. Make sure that you send the image to the back, as you need the scatter chart on top so that you can interact with the dots by clicking.

3 Arrange

4) Now the hard part is resizing the scatter chart to the size of the court. I turned off the X and Y axis, and then turned on the X,Y reference lines so that I know where the center needs to be.

4 Resizing court

5) Once you have found the right fit, you can turn off the reference lines also and then add the shot result to differentiate between made and missed attempt. You can also add a chiclet slicer with the opponent images to see the shots by teams as shown below.

4 Final shot chart

That said, there is already a custom visual called the Enhanced Scatter Plot which allows you to put an image behind a scatter plot chart. I couldn’t make my data line up with the image, and hence I had to do it the hard way. You might find it easier to use the Enhanced scatter plot directly. Hope you liked my version of the shot chart!

Posted by SQLJason in Power BI, 11 comments