Hex Tile Grid Maps for Power BI

1-Shot-Chart_thumb
NBA style Shot Charts in Power BI
February 11, 2016
Show all

Hex Tile Grid Maps for Power BI

Hex Map in Power BI

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.

8 Comments

  1. […] Source: sqljason.com Name * 4/23/2016I 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!). 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. […]

  2. […] Hex Tile Grid Maps for Power BI (@SQLJason) […]

  3. rakesh says:

    Hey Jason, Appreciate your efforts.
    thoughts engineered.

    Is this possible in SSRS 2016…?

  4. rakesh says:

    Sure, will check that one.

  5. rakesh says:

    Hi jason,

    Can you let know on how to make a
    1. Balanced Score Card (BSC) and
    2. Strategy Map feature

    Currently this feature is available in QlikView, however I do not want to recommend that product to my client as it is another skillset to be acquired.
    (http://ap-a.demo.qlik.com/QvAJAXZfc/opendoc.htm?document=qvdocs%2FFinancial%20ScoreCard.qvw&host=demo11&anonymous=true )

    Is this possible in SSRS 2016…?

    What I tried and not succeeded was : –
    1. BSC like features in Sharepoint and Dashboard Designer
    Reason to failure: – no clear documentation and no link to download dashboard designer.
    2. bambooBSC
    Reason to failure: – no clear documentation and no support for the product, so could not recommend.

    ————————————————

    Sample working input csv file used in QlikView trial version is:-

    ———————FILE OPEN————————–
    Area,Objective,Measure,Actual,Target,Responsible,,
    Financial,Enhance Shareholder Return,ROE,14.50%,13.48%,Ram,,
    Financial,Enhance Shareholder Return,ROA,1.98%,1.90%,Shyam,,
    Financial,Enhance Shareholder Return,GNPA,0.73%,-,Sagar,,
    Financial,Enhance Shareholder Return,NIM,4.35%,4.56%,Nikhil,,
    Financial,Improve Materiality/Size,Loan Book Size (in Cr.),201.00,249.00,Prafful,,
    Financial,Improve Materiality/Size,Rank in NBFC,8.00%,5.00,Shruti,,
    Financial,Improve Materiality/Size,PBT (in Cr.),40.00,45.00,Divya,,
    Financial,Optimise Cost,Cost of Funds,12.65,11.23,Priya,,
    Financial,Optimise Cost,CIR,1.19,1.00,Ajit,,
    Customer,Enhance Share of Segment,LOB Wise Book Size,25%,20%,Neha,,
    Customer,Enhance Customer Growth,Share of wallet,80%,70%,Deepika,,
    Customer,Enhance Customer Growth,Cross/Up Sell,1.50,1.20,Pranali,,
    Customer,Enhance Customer Growth,No. of new customers,123.00,10.00,Pranav,,
    Customer,Enhance Customer Loyalty,NPS%,12.00,10.00,Vivek,,
    Customer,Enhance Customer Loyalty,Retention,98%,97%,Nikita,,
    Customer,Enhance Customer Loyalty,No. of referrals ,12.00,1000.00,Roy,,
    Customer,Enhance Customer Loyalty,Complaints,5.00,500.00,Ram,,
    Customer,Enhance Partner Engagement,Partners satisfaction survey,8.00,8.00,Shyam,,
    Internal Process,Service Process Effectiveness,People dependency,65%,30%,Sagar,,
    Internal Process,Service Process Effectiveness,FTR,20%,10%,Nikhil,,
    Internal Process,Service Process Effectiveness,% of Resource for customer experience,80%,85%,Prafful,,
    Internal Process,Service Process Effectiveness,End to End TAT,35%,10%,Shruti,,
    Internal Process,Service Process Effectiveness,QRC TAT,25%,10%,Divya,,
    Internal Process,Acquisition Process Effectiveness,Leads Conversion Ratio,43%,70%,Priya,,
    Internal Process,Support Process Effectiveness,Internal Csat Survey,7.00,8.00,Ajit,,
    Internal Process,Innovation Process Effectiveness,% Rev. from NPD,35%,40%,Neha,,
    Internal Process,Innovation Process Effectiveness,Service Utilisation %,67%,80%,Deepika,,
    Internal Process,Enhance Organisational Sustainability,Slippage Ratio,10%,8%,Pranali,,
    Internal Process,Enhance Organisational Sustainability,Financial Loss,160.00,150.00,Pranav,,
    Internal Process,Improve Productivity,Leverage Ratios,4.50,3.50,Vivek,,
    Internal Process,Improve Productivity,Cost per transaction,132.00,120.00,Nikita,,
    Learning & Growth,Development of Human Capital,Engagement Score,8.00,8.00,Roy,,
    Learning & Growth,Development of Human Capital,Training Effectiveness,9.00,7.00,Prafful,,
    Learning & Growth,Development of Human Capital,Employee Retention,95%,96%,Shruti,,
    Learning & Growth,Development of Human Capital,% of Employees requiring re-skilling,15%,13%,Divya,,
    Learning & Growth,Development of Human Capital,Leadership positions for which successors are identified,95%,90%,Priya,,
    Learning & Growth,Align Objectives,% employees aligned to BSC,70%,95%,Ajit,,
    Learning & Growth,Build Knowledge Capital,% of employees participating in improvement initiatives,80%,95%,Neha,,
    Learning & Growth,Leverage Technology for Strategic Benefits,No. of processes automated,45%,90%,Divya,,

    —————–FILE CLOSE—————–

  6. rakesh says:

    Hey dude, can you take out some time and let know whether the Balanced Score Card and Strategy Maps functionality is acheivable in SSRS 2016 or Power BI or any of the Microsoft Stack….?

Leave a Reply

Your email address will not be published. Required fields are marked *