2023 Week 52 | Power BI: Format Bar Charts with Transparency, Borders, and Detail Labels

Preparing the data: 

1. Get the data from the YouGov PDF using the PDF connector. 
2. Select Table008, Table009, Table010, and Table011. 
3. Unpivot the data so that demographic attributes are contained in a single column. 
4. Combine Table008 and Table009 into a single table with the following columns: [Making Resolutions], [Attribute], [Percent].
5. Remove “Totals” and “UnweightedN” from the Making Resolutions column. 
6. Replace values in the Making Resolutions and Attribute columns to be more user friendly.
7. Combine Table010 and Table011 into a single table with the following columns: [Resolution], [Attribute], [Percent].
8. Remove “Totals” and “UnweightedN” from the Resolution column. 
9. Replace values in the Resolution and Attribute columns to be more user friendly.
10. Create a new table that contains a distinct list of values from the Attribute column of the first (Making Resolutions) table. Add a column that groups the Attributes into categories (e.g., gender, household income, age, etc.). 

Visualizing the data: 

1. Create a 100% stacked bar chart with the Attribute column on the y-axis, the percent value from the “Making Resolutions” table on the x-axis, and the Making Resolutions column in the legend. Title the chart “Likelihood of Making Resolutions”.
2. Create a bar chart with the Resolution column on the y-axis and the percent value from the Resolutions table on the x-axis. Title the chart “Top 5 Resolutions”.
3. Filter the Top 5 Resolutions chart to only show the top 5 resolutions, based upon the selected attribute in the Likelihood of Making Resolutions chart. If no attribute is selected, the chart should show the top 5 resolutions for the total group. More than 5 values may be displayed in the case of ties. 
4. On both bar charts, set the transparency of each bar color fill to 25%. 
5. On both bar charts, enable the border on the bar and set it to match the bar color.
6. Add a slicer that allows the user to select one (single-select) demographic category for which they want to see attributes in the Likelihood of Making Resolutions chart.
7. On the Top 5 Resolutions chart, add data labels that show the percent value for each resolution. Then add a detail data label with the following logic: If an attribute is selected in the Likelihood of Making Resolutions chart, the percent value for the current resolution should be compared to the percent value of that resolution for the total group. If no attribute is selected, compare the percent value of the resolutions for the total group to the first (your choice in how you define first) Attribute value. For example, when the US Region category is selected in the slicer and Northeast is selected in the top bar chart, the data label for “Eating healthier” should show both “24%” and “▲ 4% vs total”. If no US region is selected, the data label for “Eating healthier” should show both “20%” and “▲ 2% vs Midwest” (or any other single region of your choice).