My goal here was to show weekly excess mortality in Portugal and how it relates to Covid-19 deaths.
To calculate excess mortality, you need to define a reference line. Typically, we use the average number of deaths for the period 2015-2019. Since data for a more extended period (2009-2019) was available, I used it to show variability around the baseline (the median for the 2009-2019 period, taking into account population aging). You can see how, starting from March, weekly excess mortality in 2020 was significantly higher than the expected value and how tragic January 2021 was.
A bar chart displays the difference between deaths in 2021 and the baseline in the section below. A marker indicates how many of these deaths are officially attributed to Covid-19.
At the top, three KPIs indicate the cumulative values for excess deaths in absolute values and as a percentage of the baseline and the total of Covid-19 deaths.
This chart is relatively easy to make in Excel. I wrote a tutorial for Flowing Data on how to make it. I’m working now on a PowerBI version.
Design-wise, it’s interesting to note that you can seamlessly combine areas, points, and lines, overlap series, use stacked and non-stacked series. You can’t find this level of flexibility in other tools.