Huff Gravity Model Analysis in Power BI

Huff Gravity Model Analysis in Power BI

2 comments

In this tutorial, we’ll learn how to do a Huff Gravity Model analysis in Power BI. We can use this analysis to estimate the potential sales or attractiveness of a certain store location. We usually do this in a Geographic Information System software. However, we can also do it in Power BI and make it dynamic.

The Huff Gravity Analysis assumes that the surface in square meters of a supermarket store, divided by the distance squared to potential customers will result in an attractiveness factor that sets off against other stores. This will also show the probability as a percentage for visiting customers.

The assumption is based on the fact that the more square meters a store has, the greater the assortment and presence of other servicing elements will be. So, the store may attract customers to travel a longer distance.

In this example, the driving distance has been used (postcode centroid to the store).

huff gravity model

We can also use straight-line distance. However, in this case, there’s a river separating the boundaries. Thus, a straight-line distance is not reliable.

Ideally, we use smaller areas such as neighborhoods. This is for demonstration only. We can add more parameters to impact the probability like parking space, public transport, and use the methodology for other analyses as well.

We can also add a distance-decay factor to dampen the distance effect. People are prepared to travel further when shopping for furniture than they are for their daily groceries. 

Huff Gravity Model Analysis Data

First, let’s have a look at the data.

In this excel spreadsheet, there are six supermarkets.

huff gravity model data

It also has the Kilometers that contains the distance as a straight line.

huff gravity model data

Then, there’s a Travel Time tab which displays travel time in minutes.

huff gravity model data

And this one’s the distance. We’re going to use this given the fact that there’s a river between the boundaries.

huff gravity model data

This one’s a Thiessen polygon created in GIS software. This is where we can create a so-called Thiessen Voronoi object to show you the distance from a point to each of the other adjacent objects.

Thiessen polygon - huff gravity model

Importing Data In Power Query Editor

First, I imported the data into Power Query Editor.

As you can see, I’ve taken five supermarkets.

There are also two data sets here named as Postcodes Areas PQ and Postcodes Areas DAX.

I’ve duplicated this so I can show you how to do it in Power Query editor with fully dynamic measures.

For the Power Query demo (Postcodes Areas PQ), I’ve rounded off the latitude and longitude. I always advise that if you take four digits behind the comma, your accuracy will be about 11 meters, which is by far, enough.

I also calculated the square of every distance. This is because as I previously mentioned, we’re going to eventually use the surface in square meters and divide it by the distance squared.

Then, I merged it with another table (Population table) to get the population. This is to get more insights on the population in the postcode areas.

For the measures data (Postcodes Areas DAX), I also did the same thing like rounding the latitude and longitude and merged it again with the Population table.

Now, this is the Power BI dashboard of the Huff Gravity Model Analysis.

Power BI dashboard of the Huff Gravity Model Analysis

These are the measure tables that I’ve split up.

Huff Gravity Model Analysis Based On Attractiveness

The first calculation I created is the Attractiveness.

The Attractiveness is the square meters of the store divided by the Squared Distance. This store has 1,502 square meter surface.

This is the column of the Squared Distance. In this example, I’ve taken the MIN. I could have taken the MAX or the average, but it doesn’t really matter given the context.

I did that calculation for all five supermarkets.

Then, I added them up in the TotalAT measure to calculate the total.

Probability In Huff Gravity Model Analysis

The next measure is Probability.

Probability is simply how likely an event is to happen. To calculate that, a single event with a single outcome should be determined. Then, identify the total number of outcomes that can occur. Lastly, divide the number of events by the number of possible outcomes.

Therefore, I divided Attractiveness by Total Attractiveness in this calculation. 

These numbers will add up to a hundred percent.

There’s also a Population measure from the merged dataset that sums up the population based on postcode areas.

Then, the Max Probability measure.

This card is displaying that.

huff gravity model analysis

Lastly, I have a Probability of Selected Store measure. I used this measure to identify the probability of any selected store in my selection.

Let’s now discuss how it works.

Probability Analysis

As I map, I’ve taken the boundaries as postcodes. I’ve taken a four-digit postcode.

Here’s a table with the Probability of Selected Store.

huff gravity model analysis

This little map shows the actual location of the five supermarkets.

huff gravity model analysis

I can make a selection based on the stores’ postcodes from the slicer.

huff gravity model analysis

This little map (5 Stores Rotterdam) is not filtering the Choropleth map (ESRI) on the left. This is just meant to give us a clue where we are on the Choropleth map. Moreover, it helps us to subsequently see the impact on the main map.

As you can see, the darker the color, the higher the probability % for the selected store.

huff gravity model analysis

For example, I’ll select this location or supermarket.

huff gravity model analysis

If I check out this area on the map, It’ll display the probability of that store given the distance squared. Note that this is based on the driving distance.

huff gravity model analysis

The Max Probability for this selection is 95% represented on this card.

huff gravity model analysis

This part displays the included postcodes and the declining probability. The smaller the percentage, the more likely their particular postcode will be closer to another supermarket.

huff gravity model analysis

For instance, if I click this one, it’ll show that the probability is 0%.

huff gravity model analysis

Obviously, the people in this area are living on top of the supermarket under postcode 3011. So, why would they go to another one?

huff gravity model analysis

This part shows the actual store surface for reference.

huff gravity model analysis

On the other hand, this displays the total population within the selection.

huff gravity model analysis

Dynamic Huff Gravity Analysis

Now that I’m done with the basics of a Huff Gravity Analysis, I’ll go a step further and discuss how I can make this dynamic.

In this case, I created five slicers with the initial square meters and options for increasing the store area.

The rest of the steps are quite similar to the previous step. I now have a lot more measures because we need to calculate something that is dynamic. I’ve taken the steps apart to make it more insightful.

Dynamic Huff Gravity Analysis Based On Store Area

Let’s take a look at the square meter attractiveness. I’ll select the Attractiveness measure of Supermarket 3011.

The square meters will be referenced from the selected value in the 3011 slicer.

huff gravity model analysis

The distsq variable represents the distance square, which is from the Postcodes Areas DAX dataset.

In this calculation, the value of square meters will be divided by the value of distance squared.

Again, I did that for all the five supermarkets.

Dynamic Huff Gravity Analysis Based On Distance

I also calculated the distance for this analysis. It’s basically just the sum of the store’s distance column in the Postcodes Areas DAX dataset.

The selected store is being referenced in the Distance PC – Selected Store calculation using the SWITCH Dax function.

Then, I also have another probability measure for the dynamic huff gravity analysis.

It’s dynamic because if we change something in one of the slicers, it will subsequently have an impact on the outcome of the calculation.

huff gravity model analysis

I’ve gone through all those steps and calculations for the dynamic huff gravity analysis. This is because I’m interested in the percentage of the population, amount of postcodes, and the included distance based on my selection from a customized slicer.

As you can see, there’s quite a difference in the population. These are based on the distance to the supermarket and the population within the postcodes.

As an example, I’ll change the square meters of supermarket 3011.

huff gravity model analysis

Upon changing that, the impact will become evident in the data. This is because it’s more attractive for people to come into the center and go to this location given the driving distance.

Conclusion

The Huff Gravity Model analysis shows the correlation between patronage and distance from the location of the store. Hence, attractiveness and distance may possibly affect the probability of a consumer visiting a certain store.

This model can help you determine sales forecasts for business locations. Incorporating this analysis into your business model can provide a great deal of information about potential sites.

Again, this is another clear example of what we can achieve with analysis and Power BI by turning static data into a dynamic representation. 

Check out the links below for more examples and related content.

Cheers!

Paul

Membership Banne

***** Related Links *****
Data Visualizations Power BI – Dynamic Maps In Tooltips
Power BI Shape Map Visualization For Spatial Analysis
Geospatial Analysis – New Course on Enterprise DNA

***** Related Course Modules *****
Geospatial Analysis in Power BI
Data Visualization Tips
Dashboarding & Data Visualization Intensive

***** Related Support Forum Posts *****
Map Issues With Locations
Add Markers To A Region Map
Map Visualisation Type In Cohort Analysis Showcase
For more map location support queries to review see here….

2 comments on “Huff Gravity Model Analysis in Power BI”

  1. Hi Paul
    thanks for sharing such a valuable blog on forecasting store performance. I wish to follow along practically while reading the blog, kindly can you share the excel files that you have used for this analysis.
    kind regards

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.