07-04-2020 07:52 AM - last edited 07-04-2020 07:54 AM
As part of the plan to be more competent in data analysis, I recently performed data virtualizations for homeon.com.au demonstrated the short and medium-term housing market in Sydney, Australia. This task let me understand more Power BI limitations and the ways to overcome them. I would like to share the experience here and to hear any aspects of feedback and advice from you. I am in Toronto Canada and actively looking for data analysis jobs.
Part 1 Some tips for using a shape map
As the default build-in maps in PBI is very limited. I had never thought of using it. The most important part of using a sharp map virtualization is to find, edit a shape file you need, and then transform it to a TopoJSON file for import into the PBI desktop. You can try to find a shape file from a government website or Github. You can email me firstname.lastname@example.org to obtain shapes files for all LGA in Australia, suburbs, or LGA in NSW or Sydney. Another key is this awesome website https://mapshaper.org/ for both editing (select, delete, rename the key) and export to a TopoJSON. You can reference this article for the idea.
Part 2 Power BI limitations and attempt to overcome
The main data source is provided by the organization. Basically, it is a transaction table of properties sold in the past 2 decades.
The 1st limitation in some charts, the text of the legend cannot be customized. You can try to use a text format virtualization to cover the original legend.
The 2nd one is about conditional formatting. It can provide every conditional format that Excel can do. I had it showing another color in the bar with the highest value. The rule is shown in the picture. However, It is hiding in the PBI interface with “…” behind the “Default color”. (under format-data colors)
The 3rd one shows the trend between the interest rate and housing price with 2 lines in one chart. However, in PBI’s standard vituliazions options, it seems cannot having 2 completely different Y-axis lie in 2 sides of the chart. So, I had to actually use 2 charts and lay them in a certain size and position that seems like one chart. It also doesn’t have a function for a point in which charts stay in the front or back layers.
Similarly, in the following chart, we actually want to have the name of the area positioned at the end of each line with the percentage of 10 years of total growth. I believe this is the best I can do. The percentage actually used a matrix separate from the line graph.
Since there are 30+ LGA in Sydney, I separated them with 3 categories with the relative price (high/mid/low). The slicer I find from the virtualization market is very useful than the default one. I added the following table for forming the hierarchy of the slicer.
The distance-vs-price scatter chart: I actually want the name showed besides those dots instead of listed as a legend on the right side. I just could not believe this cannot be set in PBI. Is there a way to make it happened?
The last chart is showing the census statistics. The data table's structure is the following.
We want to have an effect like this:
However, I just could not find virtualization suitable for it. The current one from the virtualization market and is the best what I can find. But it is still too ugly and needs to pay for the virtualization. We chose to use another method to virtualize it.
To conclude, there are still many functions that should be added to PBI. A function for combining 2 or more charts together is really desired. On the other hand, as a data analyst, this is also why we need to learn Python for more flexible data processing, cleaning, and virtualization.
Here is the final publishing: https://homeon.com.au/reports/reportzh/report.html
Google translate said "