U2U Blog

for developers and other creative minds

Challenges in combining multiple data sets in PowerBI

Relationship between house price and income

A few days ago the media reported that Belgium was amongst one of the most expensive European countries to buy a house, if you compare the house price relative to the income. A few days ago I wrote a blog post on how we could analyze the Belgian house market using Microsoft Excel PowerBI and public data from the Belgian federal government (http://statbel.fgov.be). Inspired by the news that the house price relative to the income is an important indicator, let’s try to mimick this research, but then specifically for the Belgian house market.

With PowerPivot one cannot only model data coming from a single data source, it is also easy to combine different data sources into a single model. For this blog post, I took the model made in the previous Belgian house market analysis (with house prices per city) and then looked for a dataset with income information per city.

On http://statbel.fgov.be/nl/modules/publications/statistiques/arbeidsmarkt_levensomstandigheden/Fiscale_inkomens_-_per_gemeente_-_2008.jsp we can get fiscal income per Belgian city in 2008 (unfortunately I’m not aware of more recent public datasets on this). Using PowerQuery we can filter and convert the data such that we the total fiscal income, number of taxpayers and number of inhabitants per city.

Combining the data sets: a risky business

After we loaded this data in the model, we need to setup the proper relationships between the two data sources. Since both data sources are using the same city identifier (NIScode), this link is easily made. However, if we would directly link the table with the house prices per city (which contains a row per city per year per house type per measurement) with the table with the incomes per city (which contains a row per city per year), we can get errors. PowerPivot does not allow many-to-many relationships! In our model it is even worse. Since we currently only loaded data for 2008, we do only have one row per city in the income table. But as soon as we load data for multiple years, our model would become invalid!

image

The proper way to link data together

Even with a small data set (just 2 tables) as we have in this example, it is already important to stick to some of the core ideas in business intelligence: dimensional modeling. This boils down to splitting up data into dimensions (things you group or filter upon, typically the nouns in the business story) and facts (things you aggregate upon in your reports, typically the verbs in the business story). In our analysis we have two dimensions: Cities and years. So we must create a table with only one row per city (with all the information regarding that city: code, name, county, country), and another with one row per year. Since there is not much more to tell about a year than just its value, we can easily generate the latter table in PowerQuery:

   1: let
   2:     Source = List.Numbers(1990,25),
   3:     TableFromList = Table.FromList(Source, Splitter.SplitByNothing(), {"Year"}, null, ExtraValues.Error)
   4: in
   5:     TableFromList

After these tables get created, we can now link them in PowerPivot in this way:
image

From the perspective of linking the tables, the solution is fine. But we still run the risk that users will for instance grab the Years or NIScode column from either the HousePrice or the income table in one of their reports. This would be problematic, since this would only filter data from the table from which it was choosen, not from the other table. However, if we can force our users to select years only from the Years table and NIScodes only from the Location table, we don’t face this problem, since both our fact tables (HousePrice and Income) are related to both these dimension tables (Years and Location). This can easily be obtained by just hiding these columns in the fact tables:
image

After we made the model, we can now start defining DAX measures which are calculated over both fact tables. A very interesting one is the average house price expressed in terms of the average income: How many years of average income in a city do people need to spend to buy an average house in that city.

Why Bruges isn’t the most expensive area after all

Now we can start building PowerView reports on top of this data. In this way we can for instance see that Bruges is much more expensive than Brussels, but if we express the houseprice in terms of the income of its inhabitants, Bruges becomes cheaper than Brussels:
image

Download the Excel sheet from my OneDrive, and start building lots of interesting reports on top of this data set! Check out our Excel PowerBI course to learn more about building models and reports in Excel.

Comments (17) -

  • Burton Haynes

    4/4/2014 3:35:18 PM |

    [SALE] PROMOTE YOUR TRACKS NOW!!!

  • Issac Maez

    4/4/2014 3:46:27 PM |

    [SALE] PROMOTE YOUR TRACKS NOW!!!

  • Phone Numbers

    4/10/2014 8:08:21 PM |

    The Numbers Helpline has sourced 1000's of customer service contact <a href="www.numbershelpline.co.uk/.../";>Phone numbers</a> in the UK. We know how hard it can be to find those <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> at times that why we have made a directory with them all in one place so you will never have waste long periods of time looking for a customer service contact <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> again.

  • final cut pro studio 3

    4/11/2014 5:22:16 PM |

    Hands down, Apple's app store wins by a mile. It's a huge selection of all sorts of apps vs a rather sad selection of a handful for Zune. Microsoft has plans, especially in the realm of games, but I'm not sure I'd want to bet on the future if this aspect is important to you. The iPod is a much better choice in that case.

  • Phone Numbers

    4/14/2014 7:06:51 PM |

    The Numbers Helpline has sourced 1000's of customer service contact <a href="www.numbershelpline.co.uk/.../";>Phone numbers</a> in the UK. We know how hard it can be to find those <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> at times that why we have made a directory with them all in one place so you will never have waste long periods of time looking for a customer service contact <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> again.

  • Phone Numbers

    4/15/2014 11:04:37 PM |

    The Numbers Helpline has sourced 1000's of customer service contact <a href="www.numbershelpline.co.uk/.../";>Phone numbers</a> in the UK. We know how hard it can be to find those <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> at times that why we have made a directory with them all in one place so you will never have waste long periods of time looking for a customer service contact <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> again.

  • Phone Numbers

    4/15/2014 11:16:02 PM |

    The Numbers Helpline has sourced 1000's of customer service contact <a href="www.numbershelpline.co.uk/.../";>Phone numbers</a> in the UK. We know how hard it can be to find those <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> at times that why we have made a directory with them all in one place so you will never have waste long periods of time looking for a customer service contact <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> again.

  • cleavage tops

    4/18/2014 4:30:46 AM |

    AMAZING!

  • Damion Mcelhenney

    4/19/2014 12:48:02 PM |

    I just want to mention I am newbie to weblog and definitely liked you're web page. More than likely I’m going to bookmark your blog . You really come with good well written articles. Thank you for revealing your blog.

  • Phone Numbers

    4/22/2014 6:19:58 PM |

    The Numbers Helpline has sourced 1000's of customer service contact <a href="www.numbershelpline.co.uk/.../";>Phone numbers</a> in the UK. We know how hard it can be to find those <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> at times that why we have made a directory with them all in one place so you will never have waste long periods of time looking for a customer service contact <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> again.

  • Phone Numbers

    4/27/2014 10:31:49 PM |

    The Numbers Helpline has sourced 1000's of customer service contact <a href="www.numbershelpline.co.uk/.../";>Phone numbers</a> in the UK. We know how hard it can be to find those <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> at times that why we have made a directory with them all in one place so you will never have waste long periods of time looking for a customer service contact <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> again.

  • Phone Numbers

    4/28/2014 8:25:46 PM |

    The Numbers Helpline has sourced 1000's of customer service contact <a href="www.numbershelpline.co.uk/.../";>Phone numbers</a> in the UK. We know how hard it can be to find those <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> at times that why we have made a directory with them all in one place so you will never have waste long periods of time looking for a customer service contact <a href="www.numbershelpline.co.uk/.../";>Phonenumbers</a> again.

  • Ignacio Mullahy

    5/2/2014 12:19:35 PM |

    Very nice post, c9g8l4b9  I liked it so e8n5g0c9 much

  • real racing 3 cheats no survey

    8/7/2014 8:33:29 PM |

    It is appropriate time to make some plans for the long run and it is time to be happy. I've learn this publish and if I may just I wish to suggest you few interesting things or advice. Perhaps you can write next articles regarding this article. I desire to learn even more things approximately it!

  • Lilla Scullin

    8/15/2014 11:01:00 PM |

    Howdy very nice blog!! Guy .. Excellent .. Amazing .. I'll bookmark your blog and take the feeds additionallyI am happy to search out numerous helpful information right here within the submit, we need develop more techniques in this regard, thanks for sharing.

  • Carson Umbaugh

    8/15/2014 11:17:10 PM |

    Its like you read my mind! You seem to know a lot about this, like you wrote the book in it or something. I think that you can do with some pics to drive the message home a little bit, but instead of that, this is fantastic blog. An excellent read. I will definitely be back.

  • DEBORA Laurence

    8/16/2014 4:20:59 AM |

    annuaires-gratuit.com/ vous propose de créer gratuitement un annuaire de sites internet pour un bon référencement.

  • DEBORA Laurence

    8/24/2014 11:12:22 AM |

    annuaires-gratuit.com/ vous propose de créer gratuitement un annuaire de sites internet pour un bon référencement.

  • DEBORA Laurence

    8/24/2014 12:03:44 PM |

    annuaires-gratuit.com/ vous propose de créer gratuitement un annuaire de sites internet pour un bon référencement.

  • DEBORA Laurence

    8/24/2014 1:44:00 PM |

    annuaires-gratuit.com/ vous propose de créer gratuitement un annuaire de sites internet pour un bon référencement.

  • DEBORA Laurence

    8/25/2014 12:43:51 AM |

    annuaires-gratuit.com/ vous propose de créer gratuitement un annuaire de sites internet pour un bon référencement.

  • DEBORA Laurence

    8/25/2014 12:57:54 AM |

    annuaires-gratuit.com/ vous propose de créer gratuitement un annuaire de sites internet pour un bon référencement.

  • Bradley Goodmanson

    9/25/2014 7:41:48 AM |

    I relish, cause I discovered exactly what I used to be looking for. You've ended my 4 day lengthy hunt! God Bless you man. Have a great day. Bye

  • 12u5

    10/13/2014 2:57:43 PM |

    906553 767449I was  seeking at  some of your  articles  on this  internet site and I  believe  this  internet internet site  is  genuinely   instructive!  Maintain on posting . 961464

  • DEBORA Laurence

    11/12/2014 3:53:17 AM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

  • DEBORA Laurence

    11/12/2014 4:06:08 AM |

    Forum Annuaires-gratuit.com, annuaire de sites web et moteur de recherche sur annuaires-gratuit.com/annuaires/ .

Loading