Download the sample Power BI model
CMBI have developed this NSW Residential Sales Property Growth Power BI model using Power BI Desktop which is FREE from Microsoft.
Also interested in Power BI for Excel?
We can create Power BI models using Power BI Desktop or Excel.
Introduction to NSW Residental Sales Property Growth Power BI Model
The NSW Residential Sales Property Growth Power BI model demonstrates some key features of Power BI modelling, DAX calculations, and Power BI visualisations using data that is engaging yet relatively straightforward to understand. The workbook contains a number of worksheets which are briefly described below.
- Power Query queries and functions to import and integrate government sales data
- Power BI tables, relationships, and calculations (DAX)
- The Power BI Desktop pages and visualisations to interact with the Power BI model
Area Compare Page
The Area Compare page allows you to analyse sales property growth for houses, units, or all property in NSW for any period between 1991 and Q1 2015. The data is grouped by Local Government Area (LGA) and can be filtered by Rings which are defined by their proximity to the CBD. The Area Compare sheet uses the following features
- Power BI Slicers to choose parameters and filter data
- Column Chart visualisation with interactive filtering and drillthrough
- Hover over tooltips
Power BI Desktop interactive report
Technical details about the sample Power BI model
The Power BI Desktop model and Excel workbook are not locked and you are free to add your own visualisations and calculations to the model. If you have any feedback about the model or notice any errors or points for improvement please contact julie@cmbi.com.au
Power BI model data
The model uses Power Query to extract and integrate data from the NSW FACS Government Department website. You can obtain the original data here. CMBI have manipulated this raw data using Power Query to create the final tables seen in the model. These tables are:
- Ring - which is a grouping of government regions within NSW
- Median Sales Price Residential Property - this table contains all the median sale price data for each quarter in the period and for each Area Region
- Date - a distinct list of quarter end dates used for the median sale price data
The median price data in the model is non-aggregatable. It is not valid to add two median values together. Instead the calculations focus on comparing these median values over time. Although we cannot aggregate the median prices, the Area Region table contains geographic regions at various levels and the median prices for these aggregate region-levels are contained in the data set as pre-calculated values.
NSW Property Growth Excel Pivot Pivot Model Diagram view
Power BI model calculations
The model defines a number of calculations using DAX. DAX is similar to Excel formulas but more powerful for advanced modelling scenarios. You can inspect the formula behind any of fields by clicking on it. The DAX formula will appear in the formula bar
Power BI window showing a DAX calculation in the formula bar
DAX formulas are very powerful and can be used to solve most modelling and analytics requirements. Some features include
- Time Intelligence which can be used to calculated YTD, parallel periods, growth, and opening and closing balances
- CALCULATE statement that can manipulate the context of a calculation for complex set based analysis or advanced comparisons and correlations
- Text manipulation to add groupings, concatenate columns, or create additional descriptive attributes
- Most Excel formulas can be translated into DAX formulas. DAX also has capability beyond standard Excel formulas.
Want to learn more about Power BI?
CMBI provides onsite training for all levels of Power BI users and developers.
See our Training page to learn more about CMBI's training and mentoring programs.
About CMBI
CMBI designs and develops business intelligence and analytics solutions for clients in Sydney and regional NSW. CMBI technology expertise includes Power BI, Power Pivot, Excel, SQL Server, Analysis Services, SharePoint BI, and Tableau. Contact us today to see how we can help you make your BI goals a reality.
