Show categories with zero value on stacked bar chart

[Anonymous]

I created a conditional column that assigned  'Often', 'Sometimes', 'Rarely', or 'Never' depending on the # of times per week a person does an activity. For example, if '# Exercises per week' column = 7-10 , 'Frequency' = Often. Question: how do I create stacked bar charts that show all 4 categories in the order 'Often', 'Sometimes', 'Rarely', or 'Never' , regardless of how many people fell into each category. For example, in the 1st screenshot below, 0 people exercise 'Rarely' - how do I show that category on the graph?

[Anonymous · Accepted solution]

Please accept the solution if you find the solution helpful.

[Anonymous]

Create another table with frequency details. Use 'Sort By' column to sort the frequency by ID. Create a one-many relationship between parent table and frequency table:

1. Take [Frequency] from 'Frequency' table on Axis.

2. Count of ID from 'Frequency' table on Value.

3. Count of Frequency from 'Sheet1'/'Parent' table on Value.

4. Turn off Legend, X-Axis, Tooltip.

5 Use 'White' data color for [Count of ID] and Blue\Green color for [Count of Frequency]

6 Turn on Data Labels, Turn on Customise series under that and turn off the 'show' button.

You will have the desired output.

 

[Anonymous]

This is a huge help!!! Thank you so much! 🙂