In this post I will show you how I built this pretty chart that depicts which party controled each house of the United States Congress (the Senate and the House of Representatives) between 1968 and 1980:
The chart displays, per year, the party that controls each house and the relative majority under which control is held. The first item is given by the color of the dots and the second by their size. Hovering over any of the dots shows this:
I got the raw data from Wikipedia, and it looks like this:
I had to format the data in a special way to build the chart, especially for the year-by-year continuity. This is because elections are not held every year but are staggered. In the Senate, seats are held for six years but elections occur every two years for approximately one-third of the seats. In the House, elections are held every two years for all members. So after some M programming and a bit of DAX I got it into this shape:
To build the scatter chart, we need two measures, one for the X axis and one for the Y axis. For the X axis, I used the Begin Year and for the Y axis the House Type (1 for Senate, 2 for House). Using Controlling Party for the legend provided the colors and the size was given by the Majority Percentage. This is the final configuration:
The last touch was to use an overlaid text box to label the final Y axis:
As a final note, the Quick Insights feature of Power BI gave me the idea for this chart:
View the complete report here.
