#MakeoverMonday; 2019 wk44; Dashboard with information about different cities

Based on data from #MakeOverMonday Week 44 2019


This time I got inspired by the dataset ‘World Cities Ranked by Annual Sunshine Hours’ and by the webinar. In the webinar I saw the solution from Jack Hineman and I was like “I want to build something like this and include temperature and maybe other data aswell”.

My Idea


Let’s start with making a visualization with the World map, the Barchart Sunshine and the selection option for the city. This is the information that I can get out of the dataset from #MakeOverMonday.

Data check

First check, are there double or empty rows based on city.

  • Yes there are two city’s that are twice in the database.
  • Kampala in Uganda is a duplicate, so 1 row needs to be removed
  • I did this by creating a new column with city and country name: Text.Combine({[City], ” “, “(“, [Country], “)”})
  • I removed the one duplicate for Kampala (Uganda)
  • La Paz is also a duplicate in the City column, but it turns out to be a city in Mexico and in Boliva, so no longer a duplicate when using the new column. I checked this with information from Google Maps.
  • No empty rows.

Step 1, creating a map

When creating a map in Power BI it only visualises La Paz Boliva and not the city in Mexico when I use the city column. When I use the new column, it does not show anything on the map. So back to the Query editor to change the DAX formula into Text.Combine({[City], ” “, [Country]}) and then it shows both cities at the right spot.
A good size of the map is 1100 by 540, So I have made the canvas 1100 wide as well. The automatic zoom and zoom buttons I have removed.

Step 2, creating a barchart with sunshine hours per month per city

To get a barchart I had to arrange my data in another way. The given data was a column per month. I had to unpivot the month columns. In this way I got a table with the city and a column month and a column value.

I want a stacked column chart, with the months at the x-axis, it shorts in the wrong way, so I add a calendar table to my file. I have an excel file on my computer that I upload. I make a relation between the Month and the right column in my calendar table. Then for the x-axis I use the related column and short it on the month number column. So now when selecting a city on the map, you get the hours of Sunshine for that city. Oh, it only highlights the hours for the selected city and does not show only the bars for that city, like I want. To fix this I have made sure there is an interaction between my map and chart.

I have switched the legend off for this chart.

Every city has got its own colour. In the example I have seen in Tableau the colour did not very by city, but by number of sunshine hours. Right now, I do not know how to fix this in PowerBI, ideas are welcome. So I keep the default colours for the moment.

Step 3, adding a slicer for the city selection

I have added a slicer to choose a city and decided to move it to the top of the file, because it is the first thing to do in this visual.

The result for this moment

Next steps to take

Now I need to look for data files with the temperatures and the other information that I would like to share in this dashboard.

Plaats een reactie