This is a test visualization project for Metraj.ir. ETL is simplified by limiting data sources to flat files and reduced size of data (as per test requirements provided). Subject area are rental and buy transactions over a 4 year period.

Final result is provided as a PowerBI report file Metraj.pbix in Files directory.

Caution: You need Microsoft PowerBI desktop version installed on your computer to open .pbix files. You can Download & Install the executable from HERE

Data sources can be found in Data/Interview Project folder. Import them as UTF-8 comma delimited flat files.

ETL is quite straightforward except for handling mistyped formats for transaction dates. 13-9-2015 format had to be splited by - and reconcated as YYYY-MM-DD format

    # split dates into its date parts
    "Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", 
    "Transaction Date", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Day", "Month Number", "Year"}),
    # type conversion of parts into number
    "Change Date Parts Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Day", Int64.Type}, {"Month Number", Int64.Type}, {"Year", Int64.Type}}),
    # reconcatenae dateparts in YYYY-MM-DD format
    "Added Transaction Date" = Table.AddColumn(#"Change Date Parts Type", "Transaction Date", each #date([Year],[Month Number],[Day])),
    # Add English Months column
    "Added Month" = Table.AddColumn(#"Added Transaction Date", "Month", each Date.MonthName([Transaction Date])),

Data model is simple star schmea, with helper dimensions added for Area & Age segments.

There is no direct Sales data provied in source files, but it can be easily calculated based on common standards in Iran for Real Estate rental/buying commissions. A thorough guide can be found HERE

Below is the business logic for Commisisons written in DAX

Commission = 2 * IF([Transaction Type] = "Buy", IF(RELATED('Buy Prices'[Total Price]) <= 500000000,RELATED('Buy Prices'[Total Price]) * 0.005,2500000 + (RELATED('Buy Prices'[Total Price]) - 500000000) * 0.0025),RELATED('Rent Prices'[Final Price]) * 0.25)

Building Area and Age subjects can be segmented based on data spread and Market rules (this is valid for Age segmentation). Calculating the quartiles for Area and Age resulted in these numbers

Area

1. Q1 = 57
2. Q2 = 70
3. Q3 = 100.8

Age

1. Q1 = 2
2. Q2 = 7
3. Q3 = 13

Building Age affects the Rental/Buy price. According to this article

Mitigated Quartiles are as following

Area

1. Q1 = 50
2. Q2 = 70
3. Q3 = 100

Age

1. Q1 = 2
2. Q2 = 7
3. Q3 = 15

Visualization is provided as an Overview report of overal Sales, Segments and City shares.

This Dashboard can be further enriched in information by adding separate pages for City Analysis, Age and Area categories and Map data (a bit of hassle with data, as there is a need for translation of district names and extracting their cooridnations)