I keep seeing a particular visual shared by a Denver-area meteorologist on social media. It seems to be output from weathermodels.com.
I thought it might be fun to make a modified version in Power BI, so that is our challenge this week. While we don’t have native overlapping columns, there is a native solution achievable using error bars. If you are familiar with Deneb, you can always go that route. I have completed both a native solution and a Deneb solution below.
While this information might be better visualized with a column range chart, we are going to stay true to the original chart choice and go with a lipstick plot, also called a nested column chart.
- Import the location and temperature data from Data.World.
- Add a date table to the data model. Ensure the date table contains a column containing the date in the format “MMM dd” (e.g., “Jan 24”) and a column containing the abbreviated weekday name (e.g., “Sun”, “Mon”, etc.). Relate the temperatures table to the date table.
- Create a matrix that shows the high and low temperatures by weekday. It should use the abbreviated 3-character weekday label.
- Use conditional formatting on both the high temperature and low temperature rows in the matrix. Set the background color to gradient, using a different color (hue) for the high vs low temperature. Set the font color to ensure dark text is used on light backgrounds and light text on dark backgrounds.
- Create a column chart with nested columns, using a column chart with error bars or a custom visual. Ensure that both the high temperature and low temperature are labeled on/near each bar. Label the x-axis with the date and the weekday name. Do your best to achieve good color contrast between the page background and the bars (at 3:1 contrast) – this might require borders around the bars.
You can find this week’s data on Data.World at https://data.world/mlongoria/wowpbi2024wk4.
Data.World requires you to create a free account to access the data. You may use the Data.World connector in Power BI or download the file to your local machine and connect to it there.
There is one Excel file with two worksheets.